Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE simple_table (
    id int,
    datetime timestamp without time zone,
    category int
);

INSERT INTO simple_table VALUES
(1,'2018-05-12 5:05',1),
(2,'2018-05-12 5:10',1),
(3,'2018-05-12 6:25',2),
(4,'2018-05-13 7:40',1),
(5,'2018-05-14 8:50',4);

SELECT * FROM simple_table;


id
datetime
category


1
2018-05-12 05:05:00
1

2
2018-05-12 05:10:00
1

3
2018-05-12 06:25:00
2

4
2018-05-13 07:40:00
1

5
2018-05-14 08:50:00
4



SELECT 5


SELECT datetime::date, category, COUNT(*)
FROM simple_table
GROUP BY datetime::date, category


datetime
category
count


2018-05-13
1
1

2018-05-14
4
1

2018-05-12
1
2

2018-05-12
2
1



SELECT 4


WITH weeks AS (
    SELECT EXTRACT(week from datetime) as week, category FROM simple_table 
)
SELECT week, category, COUNT(*)
FROM weeks
GROUP BY week, category


week
category
count


19
1
3

19
2
1

20
4
1



SELECT 3

id	datetime	category
1	2018-05-12 05:05:00	1
2	2018-05-12 05:10:00	1
3	2018-05-12 06:25:00	2
4	2018-05-13 07:40:00	1
5	2018-05-14 08:50:00	4