CODEWITHSUNDEEP
sqlgoogle-bigquery
Jungleman
Jungleman

Reputation: 296

Iterative Query on the Same Table

I have a table which includes customer's sales data as follows;

|CustomerCode|SalesDate|Profit
|123455666   |2018-06  |120
|123455666   |2018-06  |100
|123455666   |2018-05  |10
|123455666   |2018-04  |60
|666452342   |2018-06  |900
|666452342   |2018-05  |1000
|666452342   |2018-05  |900
|666452342   |2018-06  |800

I want a table that shows company profits for 3 months period. Expected table;

|CustomerCode|P_This_Month|P_1_Month_Ago|P_2_Month_Ago
|123455666   |220         |10           |60
|666452342   |900         |1900         |800

What is the best way of doing that? At the moment I am using JOIN operator. However, even though it works with first join, doesn't work with the second join (got unexpected high values).

How can I do that?

SELECT 
  This_Month.*, 
  SUM(_1_Month_Ago.UCSALES) sales_1_month_ago,
  SUM(_2_Months_Ago.UCSALES) sales_2_months_ago
FROM 
  SalesTable This_Month
LEFT JOIN
  SalesTable _1_Month_Ago ON This_Month.CustomerCode = _1_Month_Ago AND DATE_SUB(This_Month.SalesDate, INTERVAL 1 MONTH) = _1_Month_Ago.SalesDate
LEFT JOIN
  SalesTable _2_Months_Ago ON This_Month.CustomerCode = _2_Months_Ago AND DATE_SUB(This_Month.SalesDate, INTERVAL 1 MONTH) = _2_Months_Ago.SalesDate
GROUP BY 
  This_Month.CustomerCode

Upvotes: 0

Views: 87

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Below is for BigQuery Standard SQL

This solution avoids making self joins and generic enough to cover data for multiple months 

#standardSQL
WITH months AS (
  SELECT FORMAT_DATE('%Y-%m', month) month
  FROM (
    SELECT 
      MIN(PARSE_DATE('%Y-%m', SalesDate)) min_month, 
      MAX(PARSE_DATE('%Y-%m', SalesDate)) max_month
    FROM `project.dataset.table`
  ), UNNEST(GENERATE_DATE_ARRAY(min_month, max_month, INTERVAL 1 MONTH)) month  
)
SELECT CustomerCode, month, 
  SUM(IF(diff = 0, P_This_Month, 0)) P_This_Month,
  SUM(IF(diff = 1, P_This_Month, 0)) P_1_Month_Ago,
  SUM(IF(diff = 2, P_This_Month, 0)) P_2_Month_Ago
FROM (
  SELECT CustomerCode, P_This_Month, PARSE_DATE('%Y-%m', month) month, 
    DATE_DIFF(PARSE_DATE('%Y-%m', month), PARSE_DATE('%Y-%m', SalesDate), MONTH) diff
  FROM months m CROSS JOIN (
    SELECT CustomerCode, SalesDate, SUM(Profit) P_This_Month
    FROM `project.dataset.table`
    GROUP BY CustomerCode, SalesDate
  ) t
)
WHERE diff BETWEEN 0 AND 2
GROUP BY CustomerCode, month

You can test, play with above using sample data from your question as in example below 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 123455666 CustomerCode, '2018-06' SalesDate, 120 Profit UNION ALL
  SELECT 123455666, '2018-06', 100 UNION ALL
  SELECT 123455666, '2018-05', 10 UNION ALL
  SELECT 123455666, '2018-04', 60 UNION ALL
  SELECT 666452342, '2018-06', 900 UNION ALL
  SELECT 666452342, '2018-05', 1000 UNION ALL
  SELECT 666452342, '2018-05', 900 UNION ALL
  SELECT 666452342, '2018-04', 800 
), months AS (
  SELECT FORMAT_DATE('%Y-%m', month) month
  FROM (
    SELECT 
      MIN(PARSE_DATE('%Y-%m', SalesDate)) min_month, 
      MAX(PARSE_DATE('%Y-%m', SalesDate)) max_month
    FROM `project.dataset.table`
  ), UNNEST(GENERATE_DATE_ARRAY(min_month, max_month, INTERVAL 1 MONTH)) month  
)
SELECT CustomerCode, month, 
  SUM(IF(diff = 0, P_This_Month, 0)) P_This_Month,
  SUM(IF(diff = 1, P_This_Month, 0)) P_1_Month_Ago,
  SUM(IF(diff = 2, P_This_Month, 0)) P_2_Month_Ago
FROM (
  SELECT CustomerCode, P_This_Month, PARSE_DATE('%Y-%m', month) month, 
    DATE_DIFF(PARSE_DATE('%Y-%m', month), PARSE_DATE('%Y-%m', SalesDate), MONTH) diff
  FROM months m CROSS JOIN (
    SELECT CustomerCode, SalesDate, SUM(Profit) P_This_Month
    FROM `project.dataset.table`
    GROUP BY CustomerCode, SalesDate
  ) t
)
WHERE diff BETWEEN 0 AND 2
GROUP BY CustomerCode, month
-- ORDER BY CustomerCode, month

with result 

Row CustomerCode    month       P_This_Month    P_1_Month_Ago   P_2_Month_Ago    
1   123455666       2018-04-01  60              0               0    
2   123455666       2018-05-01  10              60              0    
3   123455666       2018-06-01  220             10              60   
4   666452342       2018-04-01  800             0               0    
5   666452342       2018-05-01  1900            800             0    
6   666452342       2018-06-01  900             1900            800

Upvotes: 0

Vijiy
Vijiy

Reputation: 1197

You can do it with conditional aggregation.

select
CustomerCode,
sum(case when sales_date=current_date then profit else 0 end) P_This_Month,
sum(case when sales_date=date_sub(current_date, INTERVAL 1 month) then profit else 0 end) P_1_Month,
sum(case when sales_date=date_sub(current_date, INTERVAL 2 month) then profit else 0 end) P_2_Month,
from <tableName>
group by CustomerCode

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

you can use case when and by extracting month from date

    with cte as
    (
     select EXTRACT(month FROM SalesDate) as monthofday,CustomerCode,sum(Profit) as total
     from tbale

    ) select CustomerCode,
 sum(case when monthofday=4 then total else 0 end) as Forththmonthtotal,
 sum(case when monthofday=5 then total else 0 end) as fifththmonthtotal,
 --you can apply for 12 month this logic 
 from cte 
group by CustomerCode

Upvotes: 1

Related Questions