Reputation: 11
Get the yearly and monthly data for previous 3 years or 2 years where sum of sale of previous years should be in column headers. I'm a beginner to the sql and bigquery. Something as shown below.
Months | Sale 2023 | Sale 2022 | Sale 2021 |
---|---|---|---|
Jan | 50 | 40 | 50 |
Feb | 40 | 20 | 40 |
Mar | 20 | 50 | 20 |
Apr | 50 | 40 | 50 |
May | 40 | 20 | 40 |
Jun | 20 | 20 | 20 |
Jul | 50 | 50 | 20 |
Aug | 40 | 40 | 50 |
Sep | 20 | 20 | 40 |
Oct | 50 | 50 | 20 |
Nov | 40 | 40 | 50 |
Dec | 20 | 40 | 40 |
I wrote the query
WITH previous AS (
SELECT
EXTRACT(YEAR FROM sell_date) - 1 AS previous_year,
EXTRACT(MONTH FROM sell_date) AS month,
SUM(sum_sale_value) AS monthly_sellout
FROM `project.dataset.table`
WHERE
EXTRACT(YEAR FROM sell_date) = EXTRACT(YEAR FROM CURRENT_DATE())
GROUP BY
previous_year,
month
)
SELECT
month,
COALESCE(SUM(monthly_sellout), 0) AS previous_year_monthly_sellout
FROM
previous
GROUP BY
month
ORDER BY
month;
Upvotes: 1
Views: 59