Postgres 15

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

CREATE TABLE ransomware_posts (post_id serial, post_date date, post_victim int, victim_industry text) ;

INSERT INTO ransomware_posts (post_date, post_victim, victim_industry)
SELECT '20200101' :: date + random() * interval '2 years', floor(random()*1000)
     , (array['Healthcare', 'Manufacturing', 'Finance', 'Education', 'Technology', 'Construction', 'Retail', 'Agriculture'])[1+floor(random()*8)]
  FROM generate_series(1,5000) AS g

CREATE TABLE



INSERT 0 5000


DELETE FROM ransomware_posts
WHERE to_char(post_date, 'YYYY-MM') = '2021-02' AND victim_industry <> 'Agriculture'

DELETE 176


SELECT *
FROM crosstab($$
  SELECT victim_industry, to_char(post_date, 'YYYY-MM') :: text as month, count(*) :: int
  FROM ransomware_posts
  WHERE post_date >= '2021-01-01' AND post_date < '2022-01-01'
  GROUP BY victim_industry, month
  ORDER BY victim_industry, month $$
) as ct (industry text, Jan int, Feb int, Mar int, Apr int, May int, Jun int, Jul int, Aug int, Sep int, Oct int, Nov int, Dec int)


industry
jan
feb
mar
apr
may
jun
jul
aug
sep
oct
nov
dec


Agriculture
27
24
29
31
20
25
30
24
26
26
25
30

Construction
28
29
20
23
33
22
20
24
25
25
18
null

Education
26
24
27
26
26
29
17
28
18
23
30
null

Finance
34
26
24
19
30
25
34
19
24
22
29
null

Healthcare
23
24
24
22
23
29
20
25
29
27
25
null

Manufacturing
25
39
26
34
34
18
29
25
23
24
34
null

Retail
27
27
21
23
27
37
16
26
33
28
25
null

Technology
20
31
16
25
30
29
26
34
20
24
32
null



SELECT 8


CREATE TYPE months AS (jan int, feb int, mar int, apr int, jun int, jul int, aug int, sep int, oct int, nov int, dec int)

CREATE TYPE


SELECT r.victim_industry, (jsonb_populate_record(null :: months, jsonb_object_agg(lower(r.date), r.count))).*
  FROM 
     ( SELECT victim_industry, to_char(post_date, 'Mon') AS date, count(*) AS count
         FROM ransomware_posts
        WHERE post_date >= '2021-01-01' AND post_date < '2022-01-01'
        GROUP BY victim_industry, to_char(post_date, 'Mon')
    ) AS r
GROUP BY r.victim_industry


victim_industry
jan
feb
mar
apr
jun
jul
aug
sep
oct
nov
dec


Agriculture
27
24
29
31
25
30
24
26
26
25
30

Construction
28
null
29
20
33
22
20
24
25
25
18

Education
26
null
24
27
26
29
17
28
18
23
30

Finance
34
null
26
24
30
25
34
19
24
22
29

Healthcare
23
null
24
24
23
29
20
25
29
27
25

Manufacturing
25
null
39
26
34
18
29
25
23
24
34

Retail
27
null
27
21
27
37
16
26
33
28
25

Technology
20
null
31
16
30
29
26
34
20
24
32



SELECT 8

industry	jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec
Agriculture	27	24	29	31	20	25	30	24	26	26	25	30
Construction	28	29	20	23	33	22	20	24	25	25	18	null
Education	26	24	27	26	26	29	17	28	18	23	30	null
Finance	34	26	24	19	30	25	34	19	24	22	29	null
Healthcare	23	24	24	22	23	29	20	25	29	27	25	null
Manufacturing	25	39	26	34	34	18	29	25	23	24	34	null
Retail	27	27	21	23	27	37	16	26	33	28	25	null
Technology	20	31	16	25	30	29	26	34	20	24	32	null