I am trying to compute the total of customer base from 2018-01-01 till last day of the months this year to achieve a month on month look. For instance, for the month of Jan in 2022, it will be the total count of distinct customers from 2018-01-01 to 2022-01-30. For the month of feb in 2022, it will be total count of distinct customers from 2018-01-01 to 2022-02-29. Could someone enlighten me?

select count(distinct customername) from table
where billingdate between "2018-01-01" and "2022-01-30";

currently, I only get the result for first month.

result

CodePudding user response：

select count(distinct customername) from table
where billingdate between '2018-01-01' and '2022-01-30';

CodePudding user response：

I this you are expecting cumulative customer count month wise, example: in jan 2018 the customer count is 10 and in feb 2018 count is 20

jan 2018 - 10 feb 2018 - 20

what u need is

jan 2018 - 10 feb 2018 - 30

in this case group the dates and use over clause to get cumulative count

select year_month_date,sum(customer_count) over(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_customer_count_from_jan_2018 from (select year_month_date, count(distinct customername) as customer_count from (select date(extract(year from billingdate),extract(month from billingdate),1) as year_month_date, customername from table) as table group by year_month_date order by year_month_date) where year_month_date >= date(2018,1,1)