How to group data by past few weeks?

I have an original table like this,

timestamp_ID          Country     col1      col2       col3 
2021-01-04 11:00:00     US         red       phone      car    
2021-01-04 11:00:00     US         red       sms        car   
2021-01-04 12:00:00     US         red       phone      car   
2021-01-07 11:00:00     UK         red       phone      car   
2021-01-08 11:00:00     US         red       phone      airplane   
2021-01-11 11:00:00     UK         red       sms        car   
2021-01-11 11:00:00     US         green     phone      car   
2021-01-12 11:00:00     US         red       phone      car   
2021-01-16 11:00:00     CA         red       sms        car   
2021-01-18 11:00:00     US         blue       phone     airplane   
2021-01-19 11:00:00     AU         red       phone      car    
2021-01-19 11:00:00     AU         blue       phone     train 

Can I group my data using GROUP BY in SQL to get the aggregate value by each week?

My expected output will be like,

Country     col1      col2       col3   count_at_week1(2021-01-04~2021-01-10) count_at_week2  count_at_week3  ...
US         red       phone      car         2                                     0                   0
US         red       sms        car         1                                     0                   0
UK         red       phone      car         1                                     0                   0
US         red       phone      airplane    1                                     0                   0
UK         red       sms        car         0                                     1                   0
US         green     phone      car         0                                     1                   0
US         red       phone      car         0                                     1                   0
CA         red       sms        car         0                                     1                   0
US         blue       phone     airplane    0                                     0                   1
AU         red       phone      car         0                                     0                   1
AU         blue       phone     train       0                                     0                   1

      

Answer

This is a way of doing it, probably not the best way however. Personally, I feel like this list is not something you’d want to do in SSMS. But rather inside Power BI, Excel or some other software for visualizing data rather than retreiving. Do note that my example below is NOT dynamic in the sense of having more and more weeks. This specifically only works for the 3 weeks specified. Please refer to the documentation to see which date belongs to what week. This is from SSMS v18.7.1

Anyway, here goes:

    CREATE TABLE #list (
    timestamp_ID datetime,
    country varchar(255),
    col1 varchar(255),
    col2 varchar(255),
    col3 varchar(255),
)

insert into #list  (timestamp_ID, country, col1, col2, col3)
Values ('2021-01-04 11:00:00', 'US', 'red', 'phone', 'car'),
('2021-01-04 11:00:00', 'US', 'red', 'sms', 'car'),
('2021-01-04 12:00:00', 'US', 'red', 'phone', 'car'),
('2021-01-07 11:00:00', 'UK', 'red', 'phone', 'car'),
('2021-01-08 11:00:00', 'US', 'red', 'phone', 'airplane'),
('2021-01-11 11:00:00', 'UK', 'red', 'sms', 'car'),
('2021-01-11 11:00:00', 'US', 'green', 'phone', 'car'),
('2021-01-12 11:00:00', 'US', 'red', 'phone', 'car'),
('2021-01-16 11:00:00', 'CA', 'red', 'sms', 'car'),
('2021-01-18 11:00:00', 'US', 'blue', 'phone', 'airplane'),
('2021-01-19 11:00:00', 'AU', 'red', 'phone', 'car'),
('2021-01-19 11:00:00', 'AU', 'blue', 'phone', 'train ');


select *, DATEPART(Week,a.timestamp_ID) As 'week'
into #week
from #list a

select a.country, a.col1, a.col2, a.col3, count(*) as 'Amount', a.week 
into #filter
from #week a
group by a.country, a.col1, a.col2, a.col3, a.week

select a.country, 
a.col1, 
a.col2, 
a.col3, 
case when b.Amount is null then 0 else b.Amount end as 'Week 2', 
case when c.Amount is null then 0 else c.Amount end as 'Week 3', 
case when d.Amount is null then 0 else d.Amount end as 'Week 4'
from #week a
left join #filter b
on a.country=b.country and a.col1=b.col1 and a.col2=b.col2 and a.col3=b.col3 and b.week = 2
left join #filter c
on a.country=c.country and a.col1=c.col1 and a.col2=c.col2 and a.col3=c.col3 and c.week = 3
left join #filter d
on a.country=d.country and a.col1=d.col1 and a.col2=d.col2 and a.col3=d.col3 and d.week = 4
group by a.country, a.col1, a.col2, a.col3, b.Amount, c.Amount, d.Amount

drop table #list
drop table #filter
drop table #week