****************************************************************
*************************************************************************************************, **********************.
Table: orders
An eCommerce company's online order table.
col_name | col_type --------------+------------------- order_id | bigint product_id | bigint customer_id | bigint order_dt | date qty | integer unit_price_usd| float channel | varchar(20) -- mobile, desktop
Sample results
platform_category | num_customers | spend -------------------+---------------+-------------------- both | 30 | 185455.25999999992 desktop_only | 15 | 96647.46999999994 mobile_only | 5 | 33410.03999999997
Solution postgres
WITH customer_spend AS (
SELECT customer_id,
SUM(CASE WHEN channel = 'mobile' THEN qty * unit_price_usd ELSE 0 END) mobile_spend,
SUM(CASE WHEN channel = 'desktop' THEN qty * unit_price_usd ELSE 0 END) desktop_spend
FROM orders
GROUP BY customer_id
)
SELECT CASE WHEN mobile_spend >0 AND desktop_spend =0 THEN 'mobile_only'
WHEN mobile_spend =0 AND desktop_spend >0 THEN 'desktop_only'
WHEN mobile_spend >0 AND desktop_spend >0 THEN 'both'
END AS platform_category,
COUNT(DISTINCT customer_id) AS num_customers,
SUM(mobile_spend + desktop_spend) AS spend
FROM customer_spend
GROUP BY 1;
Explanation
This query is used to categorize customers based on their spending behavior across different platforms (mobile and desktop).
The first part of the query creates a temporary table called "customer_spend" using a subquery. This subquery calculates the total spend of each customer on mobile and desktop channels separately using a conditional statement.
The second part of the query uses the results from the temporary table to categorize customers into three categories: "mobile_only", "desktop_only", and "both". The query then calculates the total number of customers in each category and their total spend across both platforms.
Finally, the results are grouped by the platform category using the GROUP BY clause. This query can help a data analyst understand the distribution of customers based on their platform spending and identify any patterns or trends in customer behavior.
Expected results