Avg sales of last 7 days, 8-14 days etc. - And when you have missing dates consider that date sale as 0 in Hive SQL

Question

Avg sales of last 7 days, 8-14 days etc. - And when you have missing dates consider that date sale as 0 in Hive SQL

Data:

Required Output:

Answer 1

In Oracle, you can use:

SELECT t.id,
       c.day,
       AVG( COALESCE( t.sales, 0 ) ) OVER (
         PARTITION BY id
         ORDER BY event_date DESC
         RANGE BETWEEN 0 FOLLOWING AND 6 FOLLOWING
       ) AS avg_last_7_days,
       COALESCE(
         AVG( COALESCE( t.sales, 0 ) ) OVER (
           PARTITION BY id
           ORDER BY event_date DESC
           RANGE BETWEEN 7 FOLLOWING AND 13 FOLLOWING
         ),
         0
       ) AS avg_last_8_to_14_days
FROM   ( SELECT min_dt + LEVEL - 1 AS day
         FROM   (
           SELECT MIN( event_date ) AS min_dt,
                  MAX( event_date ) AS max_dt
           FROM   table_name
         )
         CONNECT BY LEVEL <= max_dt - min_dt + 1
       ) c
       LEFT OUTER JOIN table_name t
       PARTITION BY ( t.id )
       ON ( c.day = t.event_date )
ORDER BY id, day DESC

Which, for the sample data:

CREATE TABLE table_name ( id, sales, event_date ) AS
SELECT 'ABC', 2, DATE '2020-10-16' FROM DUAL UNION ALL
SELECT 'ABC', 5, DATE '2020-10-15' FROM DUAL UNION ALL
SELECT 'ABC', 5, DATE '2020-10-12' FROM DUAL UNION ALL
SELECT 'ABC', 1, DATE '2020-10-11' FROM DUAL UNION ALL
SELECT 'ABC', 3, DATE '2020-10-10' FROM DUAL UNION ALL
SELECT 'ABC', 3, DATE '2020-10-09' FROM DUAL UNION ALL
SELECT 'ABC', 1, DATE '2020-10-08' FROM DUAL UNION ALL
SELECT 'ABC', 4, DATE '2020-10-07' FROM DUAL UNION ALL
SELECT 'ABC', 5, DATE '2020-10-06' FROM DUAL UNION ALL
SELECT 'ABC', 2, DATE '2020-10-04' FROM DUAL UNION ALL
SELECT 'ABC', 3, DATE '2020-10-03' FROM DUAL UNION ALL
SELECT 'ABC', 2, DATE '2020-10-02' FROM DUAL UNION ALL
SELECT 'ABC', 5, DATE '2020-10-01' FROM DUAL UNION ALL
SELECT 'ABC', 4, DATE '2020-09-30' FROM DUAL UNION ALL
SELECT 'ABC', 3, DATE '2020-09-29' FROM DUAL UNION ALL
SELECT 'ABC', 2, DATE '2020-09-28' FROM DUAL UNION ALL
SELECT 'ABC', 1, DATE '2020-09-27' FROM DUAL UNION ALL
SELECT 'ABC', 3, DATE '2020-09-26' FROM DUAL;

Outputs:

ID  | DAY                 |                          AVG_LAST_7_DAYS |                    AVG_LAST_8_TO_14_DAYS
:-- | :------------------ | ---------------------------------------: | ---------------------------------------:
ABC | 2020-10-16 00:00:00 |                                      3.2 |                                        3
ABC | 2020-10-15 00:00:00 |                                      3.4 | 2.83333333333333333333333333333333333333
ABC | 2020-10-14 00:00:00 |                                        0 |                                        0
ABC | 2020-10-13 00:00:00 |                                        0 |                                        0
ABC | 2020-10-12 00:00:00 | 3.14285714285714285714285714285714285714 | 3.16666666666666666666666666666666666667
ABC | 2020-10-11 00:00:00 | 2.83333333333333333333333333333333333333 |                                        3
ABC | 2020-10-10 00:00:00 |                                        3 | 2.85714285714285714285714285714285714286
ABC | 2020-10-09 00:00:00 |                                        3 | 2.85714285714285714285714285714285714286
ABC | 2020-10-08 00:00:00 | 2.83333333333333333333333333333333333333 |                                        3
ABC | 2020-10-07 00:00:00 |                                      3.5 |                                      2.6
ABC | 2020-10-06 00:00:00 |                                      3.5 |                                     2.25
ABC | 2020-10-05 00:00:00 |                                        0 |                                        0
ABC | 2020-10-04 00:00:00 |                                        3 |                                        2
ABC | 2020-10-03 00:00:00 | 2.85714285714285714285714285714285714286 |                                        3
ABC | 2020-10-02 00:00:00 | 2.85714285714285714285714285714285714286 |                                        0
ABC | 2020-10-01 00:00:00 |                                        3 |                                        0
ABC | 2020-09-30 00:00:00 |                                      2.6 |                                        0
ABC | 2020-09-29 00:00:00 |                                     2.25 |                                        0
ABC | 2020-09-28 00:00:00 |                                        2 |                                        0
ABC | 2020-09-27 00:00:00 |                                        2 |                                        0
ABC | 2020-09-26 00:00:00 |                                        3 |                                        0

db<>fiddle here