user2022284 user2022284 - 24 days ago 10
MySQL Question

Counting users between two dates

I have a table that has just two fields - a date field and customer_id. I am looking to count the number of customer ids from each date field to current date. My query below is timing out - seems very inefficient. Is there a better way to do this?

select
t.base_date,
( select
count(distinct customer_id)
from user_base as ub
where ub.base_date >= t.base_date
and ub.base_date <= current_date
) as cts
from user_base as t

Answer

Try if this gives you same results not tested but seems the way you extracted data was not the right way of doing:

select base_date, count(distinct customer_id) as cts

from user_base

where base_date between base_date AND current_date