Big Query

Iowa Liquor Sales

Annual Sales

SELECT 
  EXTRACT(YEAR FROM date) AS year, 
  SUM(sale_dollars) AS total 
FROM `bigquery-public-data.iowa_liquor_sales.sales`
GROUP BY year
ORDER BY year;

YTY

WITH sales_annual AS(
  SELECT EXTRACT(YEAR FROM date) AS year, SUM(sale_dollars) AS sales 
  FROM `bigquery-public-data.iowa_liquor_sales.sales`
  GROUP BY year
)SELECT 
  *, 
  sales / LAG(sales) OVER(ORDER BY year) - 1 AS yty
FROM sales_annual 
ORDER BY year

Sales monthly

SELECT 
  EXTRACT(YEAR FROM date) AS year, 
  EXTRACT(MONTH FROM date) AS month, 
  SUM(sale_dollars) AS sales 
FROM `bigquery-public-data.iowa_liquor_sales.sales`
GROUP BY year, month
ORDER BY year, month

Comulative Sales monthly

WITH sales_by_monthly AS(
  SELECT 
    EXTRACT(YEAR FROM date) AS year, 
    EXTRACT(MONTH FROM date) AS month, 
    SUM(sale_dollars) AS sales 
  FROM `bigquery-public-data.iowa_liquor_sales.sales`
  GROUP BY year, month
)
SELECT 
  *, 
  SUM(sales) OVER(PARTITION BY year ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales_year
FROM sales_by_monthly
ORDER BY year, month;

Sales quarterly

SELECT 
  EXTRACT(YEAR FROM date) AS year,
  EXTRACT(QUARTER FROM date) AS quarter,
  SUM(sale_dollars) AS sales 
FROM `bigquery-public-data.iowa_liquor_sales.sales`
GROUP BY year, quarter

Moving Average Sales quarterly

WITH sales_by_quarterly AS(
  SELECT 
    EXTRACT(YEAR FROM date) AS year,
    EXTRACT(QUARTER FROM date) AS quarter,
    SUM(sale_dollars) AS sales 
  FROM `bigquery-public-data.iowa_liquor_sales.sales`
  GROUP BY year, quarter
)SELECT 
  *, 
  AVG(sales) OVER(ORDER BY year, quarter ROWS BETWEEN 4 PRECEDING AND 0 FOLLOWING) AS MovingAverage 
FROM sales_by_quarterly
ORDER BY year, quarter;

Seasonal sales in 2021 in 2021

MonthSeason
3,4,5Spring
6,7,8Summer
9,10,11Autumn
12,1,2Winter
DECLARE arr ARRAY DEFAULT ['Winter', 'Spring', 'Summer', 'Auturm'];
SELECT 
  arr[OFFSET(DIV(MOD(EXTRACT(MONTH FROM date), 12), 3))] season,
  FORMAT("%'.2f", SUM(sale_dollars)) AS total
FROM `bigquery-public-data.iowa_liquor_sales.sales`
WHERE date BETWEEN '2021-3-1' AND '2022-2-28'
GROUP BY season;

Best selling items of the annual

WITH CTE AS(
  SELECT EXTRACT(YEAR FROM date) AS year, item_number, item_description , COUNT(1) AS total
  FROM `bigquery-public-data.iowa_liquor_sales.sales`
  GROUP BY year, item_number, item_description
), CTE2 AS(
  SELECT year, item_number, item_description, total, RANK() OVER(PARTITION BY year ORDER BY total DESC) AS ranking 
  FROM CTE
)SELECT year, item_number, item_description, total 
FROM CTE2 
WHERE ranking <= 10
ORDER BY year, total DESC;

Sales Average by Vender

SELECT vendor_number, vendor_name ,AVG(sale_dollars) AS sale_avg 
FROM `bigquery-public-data.iowa_liquor_sales.sales`
GROUP BY vendor_number, vendor_name;

Replace sale_dolars null by sales average by Vender

WITH avg_sales_by_vender AS(
  SELECT vendor_number, vendor_name ,AVG(sale_dollars) AS sale_avg 
  FROM `bigquery-public-data.iowa_liquor_sales.sales`
  GROUP BY vendor_number, vendor_name
)
SELECT invoice_and_item_number, date, sale_dollars, B.sale_avg, COALESCE(sale_dollars, sale_avg)
FROM `bigquery-public-data.iowa_liquor_sales.sales` AS A
  JOIN avg_sales_by_vender AS B ON A.vendor_number = B.vendor_number
  WHERE A.sale_dollars IS NULL;