Year-over-Year Comparison
Year-over-Year Comparison is a SQL statement in the SELECT Queries category. Analytical pattern: compares current period to the same period one year ago. The syntax is SELECT date, value, LAG(value, 365) OVER (ORDER BY date) AS yoy_prev, value / LAG(value, 365) OVER (ORDER BY date) - 1 AS yoy_growth. It returns yoY comparison results. A typical example: WITH daily_sales AS ( SELECT DATE(created_at) AS sale_date, SUM(amount) AS revenue FROM orders GROUP BY DATE(created_at) ) SELECT sale_date, revenue, LAG(revenue, 365) OVER (ORDER BY sale_date) AS revenue_1yr_ago, ROUND((revenue - LAG(revenue, 365) OVER (ORDER BY sale_date)) / NULLIF(LAG(revenue, 365) OVER (ORDER BY sale_date), 0) * 100, 2) AS yoy_growth_pct FROM daily_sales ORDER BY sale_date DESC; A close relative is SELECT, which retrieves data from a table. The most fundamental SQL statement. A close relative is SELECT *, which selects all columns from a table. A close relative is SELECT DISTINCT, which returns only distinct (unique) values from the specified columns.