(1)

Different products come from the same brand. This question requires that the sales of each brand should be calculated and aggregated
WITH c as (
			SELECT a.logday, b.brand_name, SUM(a.sale_amt) as sum_sales
			FROM a INNER JOIN
					 b ON a.SKU_ID = b.SKU_ID
			WHERE a.logday BETWEEN '2017-01-01' AND '2017-12-31'
			AND user_name = 'Xiao Ming'
			GROUP BY a.logday, b.brand_name
)
SELECT *
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY brand_name ORDER BY sum_sales DESC) as rnk
			FROM c) aa
WHERE rnk < 4

[Err] ERROR:  column "rnk" does not exist
//This is because the running rules of SQL run where first, so rnk cannot be found, and there are restrictions on the use of window functions

 

(2)

WITH cte_1 as (SELECT logday, brand_name, SUM(sale_amt) as sale_amt
			   FROM a INNER JOIN 
					b ON a.SKU_ID = b.SKU_ID
			   WHERE EXTRACT(YEAR FROM logday) = 2017 AND user_name = 'Xiao Ming'
			   GROUP BY logday, brand_name
),
cte_2 as (
SELECT *,
	   (logday - (SELECT MIN(logday) FROM cte_1))+1 as idd,
	   ((logday - (SELECT MIN(logday) FROM cte_1))+1 - ROW_NUMBER() OVER(PARTITION BY brand_name ORDER BY logday)) as qty
FROM (SELECT *,
			 (sale_amt::NUMERIC / LAG(sale_amt,1) OVER (PARTITION BY brand_name ORDER BY logday)::NUMERIC)-1 as ratio
	  FROM cte_1) ojbk
WHERE ratio > 0.5)
--SELECT * FROM cte_2
SELECT logday, brand_name, sale_amt
FROM cte_2
WHERE brand_name IN(
					SELECT brand_name
					FROM cte_2
					GROUP BY brand_name
					HAVING COUNT(*) >= 3
					AND COUNT(DISTINCT qty) = 1)

 

Law 2

1.cte_2 The connection in will correspond to the brand and find out the postponed days.
2.Step 2 mark with window function qty，For each brand
3.Calculated growth 50%What should be the sales volume of

--It's finally coming out
WITH cte_1 as (
SELECT a.logday, b.brand_name, SUM(a.sale_amt) as sum_sales
FROM a INNER JOIN
	 b ON a.SKU_ID = b.SKU_ID
WHERE a.logday BETWEEN '2017-01-01' AND '2017-12-31'
AND user_name = 'Xiao Ming'
GROUP BY a.logday, b.brand_name
),
cte_2 as (
SELECT C.logday as c_logday, C.brand_name as c_brand_name,D.*, --because where Can't use window function, so can't use sum_sales > last_amt Remove the non-compliance records and keep them C.logday，and C.brand_name reach cte_3
			 COALESCE(1.5 * LAG(D.sum_sales,1) OVER (PARTITION BY C.logday, C.brand_name ORDER BY D.logday ASC), 0) as last_amt
FROM cte_1 C
		 JOIN cte_1 D 
		 ON C.brand_name=D.brand_name and D.logday between C.logday and C.logday + 3*INTERVAL '1 day'
),
cte_3 as (
SELECT *,
			 COUNT(*) OVER (PARTITION BY c_logday, c_brand_name) as qty --It's important to understand INTERVAL This part, then all C Of logday and brand_name，You can build qty
FROM cte_2
WHERE sum_sales > last_amt --Deleted 2017-01-02 The unqualified data of that day, so this part qty!=4
)
SELECT logday, brand_name, sum_sales
FROM cte_3
WHERE qty = 4

The difference between the two methods is to find the continuous day first or judge the growth rate first. The second method is modified on the basis of a great God. It is completed at 2:30 in the middle of the night. It is difficult to extract the time data island. To get a continuous date is to use a group identifier. The difference between the two methods lies in whether to judge first ↑% or three consecutive days. So when doing the test data, the interference data is intentionally added. Although I have my own dishes, I am very happy to be able to make them in two ways.

 

Summary: (1) the use of the row ﹐ number() window function in the first question

(2) the use position of window function. qty cannot be used in where

(3) the use of three-tier cte should be logical. And the use of table self join.