Calculating increasing or decreasing trend over time in MySQL

Question

I have a table store_visits with the following structure:

store_visits:
 store_name: string
 visit_count: integer
 visit_date: date

My goal is to create a query that for each store and a given date range, will calculate:

• Average Number of Visits over the date range (currently using AVG(visit_count))
• Whether store visits are increasing or decreasing
• The relative rate of increase/decrease (1 to 4 scale where 1 = low rate, 4 = high rate)

The relative rate of increase/decrease in visits is for directional purpose only. It will always be a linear scale.

I've spent a day trying to construct the MySQL query to do this, and just can't get my head around it.

Any help would be greatly appreciated.

Thanks, -Scott

Answer 1

Assuming you just want to compare the store visits in the first half of the date range to the second half, here's an example that spans the last 40 days using 2 sub-queries to get the counts for each range.

select 
  ((endVisits + startVisits)/40) average, 
  (endVisits > startVisits) increasing, 
  ((endVisits - startVisits)/(startVisits) * 100) percentChange 
from 
  (select sum(visit_count) startVisits 
    from store_visit 
    where 
      visit_date > current_date - 40 
      and visit_date <= current_date - 20) startRange,
  (select sum(visit_count) endVisits 
    from store_visit 
    where  
      visit_date > current_date - 20) endRange;

Notes

I don't know where the how you want to calculate your 1-4 increase amount, so I just made it a percentage and you can modify that to whatever logic you want. Also, you'll need to update the date ranges in the sub-queries as needed.

Edit: Just updated the average to ((endVisits + startVisits)/40) instead of ((endVisits + startVisits)/2). You could also use the avg function in your sub-queries and divide the sum of those by 2 to get the average over the whole period.