By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 10.5.12-MariaDB |
CREATE TABLE tbl_warrants_checked (
ch_id int DEFAULT 1
, status VARCHAR(20) DEFAULT 'active'
, dateChecked date
);
-- Sample data
INSERT INTO tbl_warrants_checked (dateChecked) VALUES
(current_date)
, (current_date)
, (current_date)
, (current_date)
, (current_date - INTERVAL '3' DAY)
, (current_date - INTERVAL '3' DAY)
, (current_date - INTERVAL '3' DAY)
, (current_date - INTERVAL '5' DAY)
, (current_date - INTERVAL '5' DAY)
, (current_date - INTERVAL '5' DAY)
, (current_date - INTERVAL '5' DAY)
, (current_date - INTERVAL '5' DAY)
, (current_date - INTERVAL '7' DAY)
, (current_date - INTERVAL '7' DAY)
, (current_date - INTERVAL '7' DAY)
, (current_date - INTERVAL '7' DAY)
, (current_date - INTERVAL '7' DAY)
, (current_date - INTERVAL '7' DAY)
, (current_date - INTERVAL '7' DAY)
, (current_date - INTERVAL '13' DAY)
, (current_date - INTERVAL '13' DAY)
, (current_date - INTERVAL '13' DAY)
, (current_date)
, (current_date - INTERVAL '19' DAY)
, (current_date - INTERVAL '20' DAY)
, (current_date - INTERVAL '20' DAY)
, (current_date - INTERVAL '20' DAY)
, (current_date - INTERVAL '20' DAY)
, (current_date - INTERVAL '20' DAY)
, (current_date - INTERVAL '20' DAY)
, (current_date - INTERVAL '20' DAY)
, (current_date - INTERVAL '20' DAY)
, (current_date - INTERVAL '20' DAY)
, (current_date - INTERVAL '20' DAY)
-- Get counts per day since ~1 month ago.
SELECT dateChecked
, COUNT(ch_id) AS counts
FROM tbl_warrants_checked
WHERE status = 'active'
AND dateChecked > (current_date - INTERVAL '1' MONTH)
GROUP BY dateChecked
;
| dateChecked | counts |
|---|---|
| 2021-07-11 | 1 |
| 2021-07-12 | 1 |
| 2021-07-13 | 1 |
| 2021-07-14 | 2 |
| 2021-07-15 | 1 |
| 2021-07-16 | 1 |
| 2021-07-17 | 1 |
| 2021-07-18 | 1 |
| 2021-07-19 | 1 |
| 2021-07-20 | 1 |
| 2021-07-21 | 20 |
| 2021-07-22 | 1 |
| 2021-07-28 | 3 |
| 2021-08-03 | 7 |
| 2021-08-05 | 5 |
| 2021-08-07 | 3 |
| 2021-08-10 | 6 |
-- Average of counts per day since ~1 month ago.
-- Only for days present in the set
SELECT AVG(counts) AS average
FROM (
SELECT COUNT(ch_id) AS counts
FROM tbl_warrants_checked
WHERE status = 'active'
AND dateChecked > (current_date - INTERVAL '1' MONTH)
GROUP BY dateChecked
) AS x
;
| average |
|---|
| 3.2941 |
WITH RECURSIVE dates (date) AS (
SELECT current_date UNION ALL
SELECT date - INTERVAL '1' DAY FROM dates
WHERE date > (current_date - INTERVAL '1' MONTH)
)
SELECT * FROM dates
;
| date |
|---|
| 2021-08-10 |
| 2021-08-09 |
| 2021-08-08 |
| 2021-08-07 |
| 2021-08-06 |
| 2021-08-05 |
| 2021-08-04 |
| 2021-08-03 |
| 2021-08-02 |
| 2021-08-01 |
| 2021-07-31 |
| 2021-07-30 |
| 2021-07-29 |
| 2021-07-28 |
| 2021-07-27 |
| 2021-07-26 |
| 2021-07-25 |
| 2021-07-24 |
| 2021-07-23 |
| 2021-07-22 |
| 2021-07-21 |
| 2021-07-20 |
| 2021-07-19 |
| 2021-07-18 |
| 2021-07-17 |
| 2021-07-16 |
| 2021-07-15 |
| 2021-07-14 |
| 2021-07-13 |
| 2021-07-12 |
| 2021-07-11 |
| 2021-07-10 |
-- Average of counts per day since ~1 month ago.
-- Treat missing days as 0
WITH RECURSIVE dates (date) AS (
SELECT current_date UNION ALL
SELECT date - INTERVAL '1' DAY FROM dates
WHERE date > (current_date - INTERVAL '1' MONTH)
)
SELECT AVG(COALESCE(counts, 0)) AS average
FROM dates AS d
LEFT JOIN (
SELECT dateChecked
, COUNT(ch_id) AS counts
FROM tbl_warrants_checked
WHERE status = 'active'
AND dateChecked > (current_date - INTERVAL '1' MONTH)
GROUP BY dateChecked
) AS x
ON d.date = x.dateChecked
;
| average |
|---|
| 1.7500 |