SQL query to group user activity based on time range

Question

I have a table with two columns, id and modifiedDate. I want to query this table to list each id's activity between a time range. My table looks like:

+-----+------------+
| ID  |     Date   |
+-----+------------+
| 1   | 2017.01.19 |
| 1   | 2017.01.18 |
| 1   | 2017.01.10 |
| 1   | 2017.01.09 |
| 2   | 2017.01.19 |
| 2   | 2017.01.18 |
| 2   | 2017.01.10 |
| 2   | 2017.01.09 |
+-----+------------+

Desired output: 

+-----+-----------------+------------+-------+
| ID  |     this week   | last week  | total |
+-----+-----------------+------------+-------+
| 1   |        2        |      2     |   4   | 
| 2   |        2        |      2     |   4   |
+-----+-----------------+------------+-------+

Are you using MySQL or Postgresql here? (Don't tag products not involved.) — jarlh, Jan 20 '17 at 08:38
Revert that last edit... Most people here like formatted text, not pictures. — jarlh, Jan 20 '17 at 08:39
Please revert to not having your data and desired results in a picture. If you want us to give you our time to help you, the least you can do is put a few minutes into formatting it. The reason why to not post it in pictures can be found [here](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) — Ludricio, Jan 20 '17 at 08:40
Add 4 spaces at the beginning of your data- and output-rows. That makes the formatting much better — Linkan, Jan 20 '17 at 08:51
Which dbms are you using? The answer will depend on the used product! (Tag MySQL or Postgresql, or some other dbms.) — jarlh, Jan 20 '17 at 09:01

Kamil Ibadov · Answer 1 · 2017-01-20T09:41:07.457

0

It's how query like on Oracle SQL

 SELECT id, 
  COUNT(decode(to_char(t.modifydate,'iyyy-iw'),to_char(sysdate,'iyyy-iw'),1)) this_week, 
  COUNT(decode(to_char(t.modifydate,'iyyy-iw'),to_char(sysdate-7,'iyyy-iw'),1)) prev_week, 
  COUNT(id) total
FROM  test_tbl1 t
GROUP BY  id

edited Jan 20 '17 at 09:41

answered Jan 20 '17 at 08:58

Kamil Ibadov

1,436
2
20
44

score 0 · Answer 2 · edited Jan 20 '17 at 09:13

0

This should work in MySQL

SELECT `id`, 
    COUNT(CASE WHEN WEEKOFYEAR(`date`) = WEEKOFYEAR(CURDATE()) THEN 1 END) this_week, 
    COUNT(CASE WHEN WEEKOFYEAR(`date`) = WEEKOFYEAR(CURDATE())-1 THEN 1 END) prev_week, 
    COUNT(id) total
FROM  `table`
GROUP BY `id`;

edited Jan 20 '17 at 09:13

answered Jan 20 '17 at 09:04

Rakesh

84
7

this one for TSQL `SELECT id, COUNT(CASE WHEN DATEPART(wk,[Date]) = DATEPART(wk,GETDATE()) THEN 1 END) this_week, COUNT(CASE WHEN DATEPART(wk,[Date]) = DATEPART(wk,GETDATE())-1 THEN 1 END) prev_week, COUNT(id) total FROM yourtable GROUP BY id;` – Chandan Rai Jan 20 '17 at 09:12

score 0 · Answer 3 · answered Jan 20 '17 at 09:28

You need conditional aggregation which can nicely be done using the filter clause in Postgres (since 9.4)

select id, 
       count(*) filter (where this_week), 
       count(*) filter (where last_week),
       count(*) as total
from (
  select id, 
         date_trunc('week', "date")::date = date_trunc('week', current_date)::date as this_week,
         date_trunc('week', "date")::date = date_trunc('week', current_date)::date - 7 as last_week
  from data
) t
group by id
order by id;

Alternatively you could use to_char() to reduce the date to a week/year combination:

select id, 
       to_char("date", 'iyyy-iw') = to_char(current_date, 'iyyy-iw') as this_week,
       to_char("date", 'iyyy-iw') = to_char(current_date - 7, 'iyyy-iw') as last_week
from data

SQL query to group user activity based on time range

3 Answers3