Conditional logic for comparing values across different years in MySQL

[SurfingWave]

I need help writing a MySQL query that compares data between two different years. I have a table with these columns: customer_id, fiscal_year, platform, and value.

What I’m trying to do is find all customer_id records where the value for fiscal_year = '2016' minus the value for fiscal_year = '2015' is more than 500.

I’m not sure what’s the best approach here. Should I use a CASE statement, handle this in the WHERE section, or maybe use subqueries? I’m still learning MySQL so any guidance would be helpful. This is my first time working with this kind of year-over-year comparison query.

[charlottew]

Window functions are your best bet if you’re on MySQL 8.0+. I use LAG() for year-over-year comparisons all the time - works great. Try SELECT customer_id, value, LAG(value) OVER (PARTITION BY customer_id ORDER BY fiscal_year) as prev_value FROM your_table WHERE fiscal_year IN ('2015', '2016') then wrap it in a subquery to filter where value - prev_value > 500. Beats self-joins since it only scans once. Watch out for null values if customers don’t have both years.

[emcarter]

depends on ur mysql version, but if you’ve got 8.0+, use a cte. something like:

WITH yearly_data AS (
  SELECT customer_id, 
         SUM(CASE WHEN fiscal_year='2016' THEN value END) as val_2016,
         SUM(CASE WHEN fiscal_year='2015' THEN value END) as val_2015
  FROM your_table 
  GROUP BY customer_id
)
SELECT customer_id 
FROM yearly_data 
WHERE val_2016 - val_2015 > 500

handles missing years way better than joins.

[Hazel_27Yoga]

For larger datasets, try a subquery approach. It’s cleaner: SELECT DISTINCT customer_id FROM your_table t1 WHERE fiscal_year = '2016' AND (SELECT value FROM your_table t2 WHERE t2.customer_id = t1.customer_id AND t2.fiscal_year = '2015') IS NOT NULL AND value - (SELECT value FROM your_table t2 WHERE t2.customer_id = t1.customer_id AND t2.fiscal_year = '2015') > 500. It’s more readable and you won’t get duplicate rows from joins. Just index customer_id and fiscal_year for better performance.

[SpinningGalaxy]

i think using a self join is the way to go. you can do something like this: SELECT a.customer_id FROM your_table a JOIN your_table b ON a.customer_id = b.customer_id WHERE a.fiscal_year = '2016' AND b.fiscal_year = '2015' AND (a.value - b.value) > 500. this should get you the results you need!