CREATE TABLE `sales` (
INSERT INTO `sales` (`id`, `Sales_Date`, `Customer_ID`, `Item`, `Amount`) VALUES
We need to calculate the amount for each user and month, so we will use
group by sales_month, customer_id. And then we can add a condition to make the total_amount between 2000 and 10000.
Month(Date) can calculate the month of the date.
SELECT month(Sales_Date) as sales_month, Customer_ID, sum(amount) as total_amount
SELECT month(Sales_Date) as sales_month, Customer_ID, item
SELECT month(Sales_Date) as sales_month, Customer_ID, GROUP_CONCAT(item SEPARATOR '|') as top3
First of all, we should know how to make multiple rows to one row, we can use
Then we need to process the date group by group, we use a sub query and the condition
< 3 .
And I think get the order right is also important. The first is sales_month, then customer_id, and finally amount.
with t1 as (
Pre-query t1 and t2 query for the purchase log of July and August. And query p is calculate for the MoM_Decrease for each item, we use the FULL OUTER JOIN here. Then we can add the rank for the result.
with t as (
First we use pre-query to find the rows in 2018/08. After that we can use a simple query to find the customers who have purchased in at least three consecutive.