So ideally I'd like a table output such as:

consecutive_days | max_date

for a given user

where consecutive days is the number of days a person logged something and max_date was the last date in that series.

I have a table which is

id | user_id | value | created_at | updated_at

and I can't figure out how to do this at all. I know I need to select all the dates for a given user and then order by that date. I'm thinking I'd then want to group them by the consecutive series but that's where my limited knowledge of mysql flies straight out the window.

Here is an actual screen shot of the table. ignore plan_id.

The created_at column is when an entry was added so the consecutive days would be based on the date n that column.

enter image description here

  • the number of days a person logged something I see created_at and updated_at and don't see logged_at... – Akina Jun 13 '18 at 5:41
  • If you are using 8.0 or 10.2, look at the "windowing" functions. – Rick James Jun 13 '18 at 18:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.