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;