0

I have the following table:

| email   | date_added |
-------------------------
| a@a.com | 2019-01-01 |
| b@b.com | 2019-01-01 |
| a@a.com | 2019-01-02 |
| c@c.com | 2019-01-02 |
| x@x.com | 2019-01-02 |
| a@a.com | 2019-01-03 |

I am looking for the following result

| count_unique_emails | date       |
|         2           | 2019-01-01 |
|         4           | 2019-01-02 |
|         4           | 2019-01-03 |

I have no idea where to start from. Any thoughts?

Thanks

0
SELECT COUNT(DISTINCT srctable.email), dates.date_added
FROM srctable
JOIN ( SELECT DISTINCT date_added 
       FROM srctable ) dates ON srctable.date_added <= dates.date_added
GROUP BY dates.date_added
2
  • And probably simpler in 8.0 with windowing functions.
    – Rick James
    Jan 14 '20 at 23:25
  • @RickJames Agreed, but the situation when the solution for 8+ is not applicable to OP's server because it is 5+ is more common than backward one...
    – Akina
    Jan 15 '20 at 4:50

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.