I’m trying to find how many runners signed up for each 1 week period starting from 2021-01-01. I’ve got no problem with the query but i’m unable to do a mod (%) with the date to reach a 7 day figure mark to round up my query.
---My Schema
CREATE TABLE runners (
"runner_id" INTEGER,
"registration_date" DATE
);
INSERT INTO runners
("runner_id", "registration_date")
VALUES
(1, '2021-01-01'),
(2, '2021-01-03'),
(3, '2021-01-08'),
(4, '2021-01-15');
runner_id registration_date
1 2021-01-01
2 2021-01-03
3 2021-01-08
4 2021-01-15
Desired O/P:
start_of_week signups
2021-01-01 2
2021-01-08 1
2021-01-15 1
---My Query
WITH data1 AS (
SELECT
runner_id,
registration_date,
REGISTRATION_DATE - (DATEDIFF(DAY, REGISTRATION_DATE, '2021-01-01' ) % 7) AS start_of_week
FROM runners
)
select start_of_week, count(runner_id) as signup from data1 group by start_of_week;
Error
Operand type clash: date is incompatible with int
I also tried switching the date formats to mod with 7 but didn’t work.
registration_date - ((registration_date - '2021-01-01') % 7)
Error
The data types date and varchar are incompatible in the subtract operator.