I have a table with created_at and deleted_at timestamps. I need to know, for each week, how many records existed at any point that week:

Essentially, records that were created before the end of the week and deleted after the beginning of the week.

I've tried various variations of the following but it's under-reporting and I can't work out why:

SELECT
    DATE_FORMAT(created_at, '%Y-%U') AS week,
    COUNT(*)
FROM records
WHERE
    deleted_at > DATE_SUB(deleted_at, INTERVAL (WEEKDAY(deleted_at) 1) DAY)
    AND created_at < DATE_ADD(created_at, INTERVAL 7 - WEEKDAY(created_at) DAY)
GROUP BY week
ORDER BY week

Any help would be massively appreciated!

CodePudding user response：

I would create a table wktable that looks like so (for the last 5 weeks of last year):

yrweek |  wkstart   |  wkstart   
------- ------------ ------------
202249 | 2022-11-27 | 2022-12-03
202250 | 2022-12-04 | 2022-12-10
202251 | 2022-12-11 | 2022-12-17
202252 | 2022-12-18 | 2022-12-24
202253 | 2022-12-25 | 2022-12-31

To get there, find a way to create 365 consecutive integers, make all the dates of 2022 out of that, and group them by year-week. This is an example:

CREATE TABLE wk AS
WITH units(units) AS (
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION 
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
)
,tens             AS(SELECT units *        10 AS tens             FROM units       )   
,hundreds         AS(SELECT tens  *        10 AS hundreds         FROM tens        )   
,
i(i) AS (
SELECT  hundreds  tens  units 
FROM       units 
CROSS JOIN tens 
CROSS JOIN hundreds 
)
,
dt(dt) AS (
  SELECT 
    DATE_ADD(DATE '2022-01-01', INTERVAL i DAY)
  FROM i 
  WHERE i < 365 
)
SELECT
  YEAR(dt)*100   WEEK(dt) AS yrweek
, MIN(dt) AS wkstart
, MAX(dt) AS wkend
FROM dt
GROUP BY yrweek
ORDER BY yrweek;

With that table, go:

SELECT
  yrweek
, COUNT(*) AS records
FROM wk
JOIN input_table ON wk.wkstart < input_table.deleted_at
                AND wk.wkend   > input_table.created_at
GROUP BY
  yrweek
;

CodePudding user response：

I first build a list with the records, their open count, and the closed count

SELECT
  created_at,
  deleted_at,
  (SELECT COUNT(*) 
   from records r2 
   where r2.created_at <= r1.created_at ) as new,
  (SELECT COUNT(*) 
   from records r2 
   where r2.deleted_at <= r1.created_at) as closed
FROM records r1
ORDER BY r1.created_at;

After that it's just adding a GROUP BY:

SELECT
  date_format(created_at,'%Y-%U') as week,
  MAX((SELECT COUNT(*) 
   from records r2 
   where r2.created_at <= r1.created_at )) as new,
  MAX((SELECT COUNT(*) 
   from records r2 
   where r2.deleted_at <= r1.created_at)) as closed
FROM records r1
GROUP BY week
ORDER BY week;

see: DBFIDDLE

NOTE: Because I use random times, the results will change when re-run. A sample output is: