I have datewise list of subscribers in TD table, I want distinct count of subscribers on daily, weekly, 30 days not monthly, and 90 days basis. Currently I am doing manually by giving the range of dates. Is there any function or better way to do this excercise.
I am not an IT guy.... commercial BI analyst, but love to learn SQL.
Can you show your current query?
If you want a single result row you can use different CASEs like this:
COUNT(DISTINCT CASE WHEN date_col = CURRENT_DATE THEN subscriber_no END)
COUNT(DISTINCT CASE WHEN date_col BETWEEN CURRENT_DATE - 7 AND CURRENT_DATE THEN subscriber_no END)
I would like to rephrase my question; I have list of subscribers from 1-Jan to 30-Jun. I want distinct count of subscribers on 7 days groupng basis e.g.
1-Jan to 7-Jan distinct count
8-Jan to 14-Jan distinct count
15-Jan to 21-Jan distinct count
22-Jan to 28-Jan distinct count
29-Jan to 4-Feb ditinct count
Hopefully now you will understand...... I want distinct count on 7 days starting from 1-Jan and ending on 30-Jun.