Problem Description –
Write an SQL query to report the customer_id and customer_name of customers who have spent at least $100 in each month of June and July 2020.
Return the result table in any order.
The query result format is in the following example.
Problem Link – customer order frequency
Difficulty Level – Easy
Solution –
WITH result as (
SELECT
o.customer_id,
c.name,
DATE_FORMAT(o.order_date, '%Y-%m') as order_date,
SUM(quantity * price) as total_spend
FROM Orders as o JOIN Product as p
ON o.product_id = p.product_id
JOIN Customers as c
ON o.customer_id = c.customer_id
GROUP BY 1, 2, 3
ORDER BY 1
)
SELECT
DISTINCT
customer_id,
name
FROM result
WHERE customer_id IN (SELECT customer_id FROM result
WHERE order_date = '2020-06'
AND total_spend >= 100)
AND customer_id IN (SELECT customer_id FROM result
WHERE order_date = '2020-07'
AND total_spend >= 100)To solve this problem first we will join all the tables and calculate the total amount spend by each customer and month i.e we will group the data by customer and month and then calculate the total amount spend.
WITH result as (
SELECT
o.customer_id,
c.name,
DATE_FORMAT(o.order_date, '%Y-%m') as order_date,
SUM(quantity * price) as total_spend
FROM Orders as o JOIN Product as p
ON o.product_id = p.product_id
JOIN Customers as c
ON o.customer_id = c.customer_id
GROUP BY 1, 2, 3
ORDER BY 1
)
SELECT * FROM result
Our next task is to filter only the customers who have spent at least $100 in each month of June and July 2020. For that we will write two separate inner queries. One will select all customers who has spent at least $100 in month of June and another one will select the customers who has spent at least $100 in month of July. Then in the WHERE clause of outer query we use an AND operation to combine these two results.
WITH result as (
SELECT
o.customer_id,
c.name,
DATE_FORMAT(o.order_date, '%Y-%m') as order_date,
SUM(quantity * price) as total_spend
FROM Orders as o JOIN Product as p
ON o.product_id = p.product_id
JOIN Customers as c
ON o.customer_id = c.customer_id
GROUP BY 1, 2, 3
ORDER BY 1
)
SELECT
DISTINCT
customer_id,
name
FROM result
WHERE customer_id IN (SELECT customer_id FROM result
WHERE order_date = '2020-06'
AND total_spend >= 100)
AND customer_id IN (SELECT customer_id FROM result
WHERE order_date = '2020-07'
AND total_spend >= 100)