First 7 days sales-CodePudding

I want to check the sum of the amount for an item from its first day of sale next 7 days. Basically, I want to check the sum of sales for the first 7 days.

I am using the below query.

select item, sum(amt)
from table
where first_sale_dt = (first_sale_dt   6).

When I run this query, I don't get any results.

CodePudding user response：

Your code as it stands will give you no results, because you are looking at each row, and asking is the value first_sale_dt equal to a values it is not 6

You need to use a WINDOW function to look across many rows, OR self JOIN the table and filter the rows that are joined to give the result you want.

so with the CTE of data for testing:

WITH data as (
    select * from values
    (1, 2,  '2022-03-01'::date),
    (1, 4,  '2022-03-04'::date),
    (1, 200,'2022-04-01'::date),
    (3, 20, '2022-03-01'::date)
    t(item, amt, first_sale_dt)
)

this SQL show the filtered row that we are wanting to SUM, it is using a sub-select (which could be moved into a CTE) to find the "first first sale" to do the date range of.

select a.item, b.amt
from (
    select 
        item, 
        min(first_sale_dt) as first_first_sale_dt 
    from data
    group by 1
    ) as a
join data as b
   on a.item = b.item  and b.first_sale_dt <= (a.first_first_sale_dt   6)

ITEM	AMT
1	2
1	4
3	20

and therefore with a SUM added:

select a.item, sum(b.amt)
from (
    select 
        item, 
        min(first_sale_dt) as first_first_sale_dt 
    from data
    group by 1
    ) as a
join data as b
   on a.item = b.item  and b.first_sale_dt <= (a.first_first_sale_dt   6)
group by 1;

you get:

ITEM	SUM(B.AMT)
1	6
3	20

Sliding Window:

This is relying on dense data (1 row for every day), also the sliding WINDOW is doing work that is getting thrown away, which is a string sign this is not the performant solution and I would stick to the first solution.

WITH data as (
    select * from values
    (1, 2,  '2022-03-01'::date),
    (1, 2,  '2022-03-02'::date),
    (1, 2,  '2022-03-03'::date),
    (1, 2,  '2022-03-04'::date),
    (1, 2,  '2022-03-05'::date),
    (1, 2,  '2022-03-06'::date),
    (1, 2,  '2022-03-07'::date),
    (1, 2,  '2022-03-08'::date)
    t(item, amt, first_sale_dt)
)
select item,
    first_sale_dt,
    sum(amt) over(partition by item order by first_sale_dt rows BETWEEN current row and 6 following ) as s
    ,count(amt) over(partition by item order by first_sale_dt rows BETWEEN current row and 6 following ) as c
from  data
order by 2;

ITEM	FIRST_SALE_DT	S	C
1	2022-03-01	14	7
1	2022-03-02	14	7
1	2022-03-03	12	6
1	2022-03-04	10	5
1	2022-03-05	8	4
1	2022-03-06	6	3
1	2022-03-07	4	2
1	2022-03-08	2	1

thus you need to then filter out some rows.

WITH data as (
    select * from values
    (1, 2,  '2022-03-01'::date),
    (1, 2,  '2022-03-02'::date),
    (1, 2,  '2022-03-03'::date),
    (1, 2,  '2022-03-04'::date),
    (1, 2,  '2022-03-05'::date),
    (1, 2,  '2022-03-06'::date),
    (1, 2,  '2022-03-07'::date),
    (1, 2,  '2022-03-08'::date)
    t(item, amt, first_sale_dt)
)
select item,
    sum(amt) over(partition by item order by first_sale_dt rows BETWEEN current row and 6 following ) as s
from  data
qualify row_number() over (partition by item order by first_sale_dt) = 1

gives:

ITEM	S
1	14

CodePudding user response：

If you really want to use window function. Here is beginner friendly version

with cte as 
    
(select *, min(sale_date) over (partition by item) as sale_start_date 
 from data) --thanks Simeon
        
select item, sum(amt) as amount
from cte
where sale_date <= sale_start_date   6 --limit to first week
group by item;

On a side note, I suggest using dateadd instead of on dates