Question 21328 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL query for records that existed at any point each week

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:

week records
2022-01 4
2022-02 5
… …

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!

Update: I found How to populate a table with a range of dates? and propose this question be closed

Tags:

2

Answers


  1. 0

    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
;
    Login or Signup to reply.
  2. 0

    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:

    week new closed
    2022-00 31 0
    2022-01 298 64
    2022-02 570 212
    2022-03 800 421
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search