Getting % variation between 2021 and 2022 years

[LFicz]

Hey there people.

I have a dataset in big query and I need to calculate the % variation between 2 years (2021 and 2022). But my columns are very limited:

I have a column that shows the sales date, another one with the product value and another one with the supplier name.

(Sum all 2022 sales / sum all 2021 sales) -1 that is what I need to do and bring it back all results by supplier.

Is there a way to do that?

[jbocachica · Accepted solution]

Hi, using a dataset from bigquery-public-data

This is a sample using 2019 and 2020 but is the same for your dataset, also, this query will help you in future years.

WITH fullresults as 
(SELECT EXTRACT(YEAR FROM date) AS year, sum(sale_dollars) total_sales, vendor_name 
 FROM bigquery-public-data.iowa_liquor_sales.sales
 where EXTRACT(YEAR FROM date) in (2019, 2020)
 group by EXTRACT(YEAR FROM date), vendor_name)

 SELECT f1.year previous_year, f2.year current_year,
 f1.total_sales sales_previous_year, f2.total_sales sales_current_year, 
 (f2.total_sales/f1.total_sales)-1 percentage, 
 f1.vendor_name
   FROM fullresults f1 
INNER JOIN 
fullresults f2 
on f1.year+1 = f2.year and f1.vendor_name = f2.vendor_name

Regards from Colombia

John

[RC1]

@LFicz 

SELECT
  current_year,
  previous_year,
  vendor_name,
  current_sales,
  prev_sales,
  IF(prev_sales=0 , null, (current_sales/prev_sales)-1 ) as percent_variation
FROM (
  SELECT
    year AS current_year,
    LAG(year, 1, NULL) OVER(PARTITION BY vendor_name ORDER BY year ASC) AS previous_year,
    vendor_name,
    total_sales AS current_sales,
    LAG(total_sales, 1, 0) OVER(PARTITION BY vendor_name ORDER BY year ASC) AS prev_sales
  FROM (
    SELECT
      EXTRACT(YEAR
      FROM
        date) AS year,
      SUM(sale_dollars) total_sales,
      vendor_name
    FROM
      bigquery-public-data.iowa_liquor_sales.sales
    WHERE
      EXTRACT(YEAR
      FROM
        date) IN (2019,
        2020)
    GROUP BY
      EXTRACT(YEAR
      FROM
        date),
      vendor_name) )