pavan katapur
pavan katapur

Reputation: 11

How to get the sale data for previous three years in bigquery

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

Answers (0)

Related Questions