2 years ago

Note CTE (Common Table Expressions) are a MySQL feature since v8.0. My passion project these days is a Laravel app for cycling data from the https://strava.com/ API. I have a Charts section which plots ride distances for all or individual bikes on a bar chart, grouped by year or month. Notice how there should be gaps in the graph for months without rides for the selected filters (this particular bike), but the graph skips those.

-- CTE start date ) AS ( DATE(LAST_DAY(MIN(start_date))) rides user_id = XXX AND bike_id = YYY UNION ALL DATE(LAST_DAY(date)) + INTERVAL 1 MONTH dates DATE(LAST_DAY(date))