Problem

Back Market is a e-commerce company French , created in 2014, which serves as an intermediary between individuals and professionals du reconditioning of electrical and electronic devices. It has offices in Paris, Bordeaux, Berlin and counting, in 2021, just over 600 employees. This is SQL assessment to analyse analytical skills

This Back Market SQL project involves analyzing payment data using three tables: "MERCHANT," "ORDERLINE," and "PAYMENT." Below is a brief overview of the tables and key concepts for analysis:

for more detailed analysis please click here

Tables:

MERCHANT:
- MERCHANT_ID_ANONYMIZED: Primary key, unique ID for each seller.
- COUNTRY_CODE: BackMarket website code from which the client made the purchase.
- PAYPAL_START: Date when the merchant started offering PayPal.
- FIRST_ORDER: Date of the first order.
ORDERLINE:
- ORDERLINE_ID: Primary key, ID of each orderline (product in the basket).
- DATE_DELIVERY_ORDER: Delivery date of the order.
- DATE_PAYMENT_ORDER: Payment date of the order.
- PRODUCT_ID: ID of the product.
- PRODUCT_TITLE: Title of the product.
- BRAND: Brand of the product.
- CATEGORY_3_NAME: Category of the product.
- MERCHANT_ID_ANONYMIZED: Unique ID of the merchant.
- QUANTITY: Quantity on the orderline.
- PRICE: Price of the product (multiplied by quantity).
- SHIPPING_PRICE: Shipping fee on the orderline.
- PAYMENT_ID: Unique ID for payment attempt.
- STATE_ORDERLINE: Orderline state (0 = New, 1 = Paid, 2 = Accepted, 3 = Delivered, 4 = Canceled, 5 = Refunded before shipping, 6 = Refunded after shipping, 7 = Not Paid).
PAYMENT:
- PAYMENT_ID: Primary key, unique ID for each payment attempt.
- PAYMENT_INTENTION_ID: Unique ID grouping successive payment attempts of a client on a given day until payment success.
- CLIENT_ID_ANONYMIZED: Unique ID of the client.
- DATE_CREATION: Payment date.
- COUNTRY_CODE: BackMarket website code for the client's purchase.
- PAYMENT_METHOD: Method used for payment (CARD, PAYPAL, ONEY, APPLE_PAY).
- BANK_COUNTRY: Country of the client's bank.
- BANK_ANONYMIZED: Anonymized bank of the client (available for credit card transactions).
- PRICE_TO_PAY: Final price paid by the client.
- CURRENCY: Currency of the transaction.
- ERROR_GROUP: Type of payment error (DROP, TECHNICAL, FRAUD, AUTHORIZATION).
- STATE: Payment state (0 = failed, 1 = success).

Payment KPIs Definitions:

Further definitions and KPIs for analysis are provided in the "Payment definitions" tab.

The goal is likely to perform various SQL queries and analyses on this data to derive meaningful insights into payment transactions, merchant behavior, and orderline statuses. Tasks could include calculating success rates, identifying trends, and understanding payment errors. The assessment is designed to evaluate analytical skills in handling e-commerce payment data.

Solution:

TOOLS : SQL

Write a SQL query which highlights the top 5 merchants with the biggest sales per country (we assume that 1 EUR = 1 = USD)
SELECT
MERCHANT_ID_ANONYMIZED AS Merchant_ID,
COUNTRY_CODE,
SUM(o.PRICE) AS TOTAL_SALES
FROM
MERCHANT m
JOIN ORDERLINE o ON m.MERCHANT_ID_ANONYMIZED = o.MERCHANT_ID_ANONYMIZED
WHERE
STATE_ORDERLINE IN (2, 3) -- Include both 'Accepted' and 'Delivered' states
GROUP BY
1,
2
ORDER BY
3 DESC
LIMIT
5;
Write a SQL query which highlights the top 5 merchants with the biggest sales per country and the percentage of total sales in this country (we assume that 1 EUR = 1 = USD)
WITH COUNTRY_SALES AS (
SELECT
COUNTRY_CODE,
SUM(o.PRICE) AS TOTAL_SALES_COUNTRY
FROM
MERCHANT m
JOIN ORDERLINE o ON m.MERCHANT_ID_ANONYMIZED = o.MERCHANT_ID_ANONYMIZED
GROUP BY
1
)
SELECT
MERCHANT_ID_ANONYMIZED,
COUNTRY_CODE,
SUM(o.PRICE) AS TOTAL_SALES,
(SUM(o.PRICE) / cs.TOTAL_SALES_COUNTRY) * 100 AS PERCENTAGE_IN_TOTAL_COUNTRY_SALES
FROM
MERCHANT m
JOIN ORDERLINE o ON m.MERCHANT_ID_ANONYMIZED = o.MERCHANT_ID_ANONYMIZED
JOIN COUNTRY_SALES cs ON cs.COUNTRY_CODE = m.COUNTRY_CODE
WHERE
STATE_ORDERLINE IN (2, 3) -- Include both 'Accepted' and 'Delivered' states
GROUP BY
1,
2
ORDER BY
3 DESC
LIMIT
5;
Write a SQL query which highlights customer who failed repeatedly
SELECT
CLIENT_ID_ANONYMIZED AS CLIENT_ID,
PAYMENT_INTENTION_ID,
COUNT(PAYMENT_ID) AS No_of_failure_attempts
FROM
PAYMENT
WHERE
STATE = 0
GROUP BY
1,
2
HAVING
COUNT(PAYMENT_ID) > 1
ORDER BY
3 DESC;
Write a SQL query giving the percentage of client which were proposed a paypal payment but did not chose it per country
WITH payment_method AS (
SELECT
COUNTRY_CODE,
COUNT(DISTINCT CASE WHEN m.PAYPAL_START IS NOT NULL THEN p.CLIENT_ID_ANONYMIZED END) AS total_clients_offered_paypal,
COUNT(DISTINCT CASE WHEN m.PAYPAL_START IS NOT NULL AND p.PAYMENT_METHOD <> 'PAYPAL' THEN p.CLIENT_ID_ANONYMIZED END) AS clients_not_choosing_paypal
FROM
MERCHANT m
JOIN ORDERLINE o ON m.MERCHANT_ID_ANONYMIZED = o.MERCHANT_ID_ANONYMIZED
JOIN PAYMENT p ON o.PAYMENT_ID = p.PAYMENT_ID
GROUP BY
COUNTRY_CODE
)
SELECT
COUNTRY_CODE,
(clients_not_choosing_paypal / total_clients_offered_paypal) * 100 AS percentage_not_paypal
FROM
payment_method;
Write a SQL query giving the Successful Checkout Rate (SCR)
SELECT
(COUNT(DISTINCT p.PAYMENT_INTENTION_ID) / COUNT(DISTINCT p.CLIENT_ID_ANONYMIZED)) AS SCR
FROM
PAYMENT p
WHERE
p.STATE = 1; -- Include only successful payments

BackMarket Order Payment SQL analysis