Get count of active users for each day

Question

I have a data which gives idea about user when did the user subscribed to a service, in a city and when it will expire.

It is as below

+------+------------+------------+
| City | Start_Date |  End_Date  |
+------+------------+------------+
| LA   | 2019-06-01 | 2019-06-03 |
| LA   | 2019-06-07 | 2019-06-10 |
| LA   | 2019-06-09 | 2019-06-11 |
| LA   | 2019-06-13 | 2019-06-14 |
| LO   | 2019-06-01 | 2019-06-05 |
| LO   | 2019-06-04 | 2019-06-05 |
| LO   | 2019-06-07 | 2019-06-09 |
| LO   | 2019-06-08 | 2019-06-09 |
+------+------------+------------+

I want count of active users for each city for each day at a given date

Output should be somewhat similar to

+------+------------+-------+
| City |    Day     | Count |
+------+------------+-------+
| LA   | 2019-06-01 |     1 |
| LA   | 2019-06-02 |     1 |
| LA   | 2019-06-03 |     1 |
| LA   | 2019-06-04 |     0 |
| LA   | 2019-06-05 |     0 |
| LA   | 2019-06-06 |     0 |
| LA   | 2019-06-07 |     1 |
| LA   | 2019-06-08 |     1 |
| LA   | 2019-06-09 |     2 |
| LA   | 2019-06-10 |     2 |
| LA   | 2019-06-11 |     1 |
| LA   | 2019-06-12 |     0 |
| LA   | 2019-06-13 |     1 |
| LA   | 2019-06-14 |     1 |
| LA   | 2019-06-15 |     0 |
+------+------------+-------+

The output I have shown is only for City LA and I also want similar output for every city in the table.

Description of my output

In City = LA on Day = 2019-06-01 there was 1 user with active subscription, similarly for Day = 2019-06-02.
In City = LA on Day = 2019-06-09 there were 2 users with active subscription and so on.

Any help will be appreciated

SQL Fiddle Link

Create a calendar help table (or recursive cte) that returns all dates of interrest. Outer join.' — jarlh, Jun 10 '19 at 10:09
@jarlh I am pretty much new to SQL and have no idea about advance SQL concepts. If you could try it out on SQL fiddle link that will be a real help — Rookie_123, Jun 10 '19 at 10:11
Contrary to the sqlserver tag the fiddle is MySql . Which one is really your dbms? — Serg, Jun 10 '19 at 10:17
Unfortunately not every sqlserver query will run on MySql. This is the problem. — Serg, Jun 10 '19 at 10:21

Gordon Linoff · Accepted Answer · 2019-06-10T10:48:37.823

3

You need a range of dates. A numbers or tally table is handy. But a recursive CTE is also helpful.

Then use a CROSS JOIN to assign the rows, a LEFT JOIN to bring in the values you want, and thenGROUP BY to get the counts:

with dates as (
      select convert(date, '2019-06-01') as dte
      union all
      select dateadd(day, 1, dte) as dte
      from dates
      where dte < '2019-06-15'
     )
select c.city, d.dte, count(t.city)
from (select distinct city from t) c cross join
     dates d left join
     t
     on t.city = c.city and t.start_date <= d.dte and d.end_date >= t.dte 
group by c.city, d.dte
order by c.city, d.dte;

Here is a db<>fiddle -- using SQL Server.

edited Jun 10 '19 at 10:48

answered Jun 10 '19 at 10:40

Gordon Linoff

1,242,037
58
646
786

Exactly. Not only calendar tally, a set of 'cells' representing every position of interest, date + city namely. – Serg Jun 10 '19 at 11:07
I am not understanding which table is t – Rookie_123 Jun 10 '19 at 12:27
@Rookie_123 . . . `t` is your table. It is unnamed in your question so I just gave it a simple name. – Gordon Linoff Jun 10 '19 at 12:33

Claudio Corchez · Answer 2 · 2019-06-10T11:24:04.753

0

Like this should work:

select city, '2019-06-11', count(*)
from table1
where end_date >= '2019-06-11'
group by 1

You should replace the date with a variable and put inside the date you need.

edited Jun 10 '19 at 11:24

answered Jun 10 '19 at 10:23

Claudio Corchez

300
2
12

The OP is asking for a solution that returns `count`s for all days and `city` values, not one specific date. – iamdave Jun 10 '19 at 11:40

Cato · Answer 3 · 2019-06-10T10:41:47.573

First make this Useful Tally Table View

/****** Object:  View [dbo].[cteTally]    Script Date: 10/06/2019 11:02:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally


GO

Then you can generate a list of dates you are inquiring about like this (this one query below is by way of demonstration, not a finished coding)

Declare @Start as date = '20170101';
Declare @End as date = '20190601';

SELECT dateadd(day, N-1,@Start) aDate from [dbo].[cteTally] WHERE  dateadd(day, N-1,@Start) <= @End;

Now we can expand that to count records where there is a date range match

SELECT Y.City, DQ.aDate, Count(Y.City) as DayCountForCity FROM (SELECT dateadd(day, N-1,@Start) aDate from [dbo].[cteTally] WHERE  dateadd(day, N-1,@Start) <= @End) DQ
        LEFT JOIN YourTable Y ON DQ.adate BETWEEN Y.Start_Date AND Y.End_Date   
        GRoup By Y.City, DQ.adate

Try this with your given test table

Declare @Start as date = '20170101';
Declare @End as date = '20450601';

SELECT * FROM (SELECT Y.City, DQ.aDate, Count(Y.City) as DayCountForCity FROM (SELECT dateadd(day, N-1,@Start) aDate from [dbo].[cteTally] WHERE  dateadd(day, N-1,@Start) <= @End) DQ
        LEFT JOIN Table1 Y ON DQ.adate BETWEEN Y.Start_Date AND Y.End_Date  
        GRoup By Y.City, DQ.adate) Q1 WHERE Q1.city is not null
        order by city, adate

Alright then Cato, it gives wrong result, all rows return 0. — Ilyes, Jun 10 '19 at 10:38
not all rows, but it is keeping cities of NULL - making it look like that - so i made a Mark II using the given test data - hope that helps — Cato, Jun 10 '19 at 10:42

score 0 · Answer 4 · answered Jun 10 '19 at 10:42

Try below query. Test data:

DECLARE @tbl TABLE (City varchar(5),Start_Date date,End_Date date);
INSERT INTO @tbl values
( 'LA','2019-06-01','2019-06-03' ),
( 'LA','2019-06-07','2019-06-10' ),
( 'LA','2019-06-09','2019-06-11' ),
( 'LA','2019-06-13','2019-06-14' ),
( 'LO','2019-06-01','2019-06-05' ),
( 'LO','2019-06-04','2019-06-05' ),
( 'LO','2019-06-07','2019-06-09' ),
( 'LO','2019-06-08','2019-06-09' );

Actual query with CTE as calendar function:

DECLARE @start DATE, @end DATE;
SELECT @start = MIN(Start_Date), @end = MAX(End_Date) FROM @tbl;

;WITH cte AS (
    SELECT @start dt
    UNION ALL
    SELECT DATEADD(day, 1, dt) FROM CTE
    WHERE dt < @end
)

SELECT cte.dt, SUM(CASE WHEN t.City IS NULL THEN 0 ELSE 1 END) FROM cte
LEFT JOIN @tbl t 
ON cte.dt BETWEEN t.Start_Date AND t.End_Date AND t.City = 'LA'
GROUP BY cte.dt

score 0 · Answer 5 · answered Jun 10 '19 at 11:12

You can also do as

DECLARE @MN DATETIME = (SELECT MIN(Start_Date) FROM T),
        @MX DATETIME = (SELECT MAX(End_Date) FROM T);

;WITH CTE AS
(
  SELECT @MN D
  UNION ALL
  SELECT DATEADD(Day, 1, D)
  FROM CTE
  WHERE D <= @MX
)
SELECT TT.City,
       CTE.D [Day],
       (
        SELECT COUNT(1) 
        FROM T T1 
        WHERE T1.City = TT.City 
              AND CTE.D BETWEEN T1.Start_Date AND T1.End_Date
       ) Cnt
FROM CTE CROSS JOIN (VALUES('LA')) TT(City);

Returns:

+------+---------------------+-----+
| City |         Day         | Cnt |
+------+---------------------+-----+
| LA   | 01/06/2019 00:00:00 |   1 |
| LA   | 02/06/2019 00:00:00 |   1 |
| LA   | 03/06/2019 00:00:00 |   1 |
| LA   | 04/06/2019 00:00:00 |   0 |
| LA   | 05/06/2019 00:00:00 |   0 |
| LA   | 06/06/2019 00:00:00 |   0 |
| LA   | 07/06/2019 00:00:00 |   1 |
| LA   | 08/06/2019 00:00:00 |   1 |
| LA   | 09/06/2019 00:00:00 |   2 |
| LA   | 10/06/2019 00:00:00 |   2 |
| LA   | 11/06/2019 00:00:00 |   1 |
| LA   | 12/06/2019 00:00:00 |   0 |
| LA   | 13/06/2019 00:00:00 |   1 |
| LA   | 14/06/2019 00:00:00 |   1 |
| LA   | 15/06/2019 00:00:00 |   0 |
+------+---------------------+-----+

Live Demo

Can this be made more generic. I mean instead of just 'LA' can it be general ? — Rookie_123, Jun 10 '19 at 11:45

score 0 · Answer 6 · answered Jun 10 '19 at 11:56

0

SELECT date,city,count(id) as total FROM table WHERE STR_TO_DATE("2019-06-01","%Y-%m-%d") = STR_TO_DATE("2019-06-01","%Y-%m-%d") GROUP BY city

answered Jun 10 '19 at 11:56

kishan

16
3

please provide an explanation to your answer. – d_kennetz Jun 10 '19 at 13:57

Get count of active users for each day

6 Answers6