MySQL query to get sum of user and group by week

Question

I have two tables user_profile and tracked_search. The user_profile table has user details and tracked_search tracks search made by user.

Whenever a user makes a search this search entry goes in tracked_search table. If nothing is searched for a particular date nothing is added in tracked_search.

I need to develop a report where in I need to show data from last 6 months for every week for example

Date       week_count   user_count
2017-05-01    18           10
2017-05-08    19           50
    .
    .
2017-07-03   27            80
    .
2017-10-2    40           20
   .
   .
2017-10-23   43           40

the data should be grouped by week and user_count is number of distinct user who made search for that week. even if there is no data for a particular week it should print the week date, week count and user count as 0

my tables as follows

User_profile
user_id  user_name user_emailId        user_passsword   user_role creation_date 
  1        Mac       mac@yahoo.com      password123       USER     23/10/2017
  2        Shane     Shane@yahoo.com    password123       USER     23/10/2017

  Tracked_search
  id      created             content  search_term,  user_id
014af54e 2017-10-15 18:36:49  ARTICLE     latest       1
08f55f2d 2017-10-18 18:34:04  EVENT       upcoming     1
1e74f026 2017-10-25 18:37:11  DISCUSSION  newest       2
20075e4a 2017-10-22 18:35:41  ARTICLE    latest        1
22cde973 2017-10-17 18:36:49  ARTICLE     latest       2
2d1d3314 2017-10-16 18:36:49  ARTICLE     latest       2

I think I just saw you post this as an R question, but that aside, you need to show us at least your table structure, ideally some sample data which generates that output. — Tim Biegeleisen, Oct 30 '17 at 11:02
in user_profile table i have only user details and user_id, while in tracked_search i have id, user_id, date and what thing he searched ie a string — Suyash, Oct 30 '17 at 11:06
@Suyash, please update your question accordingly, with the actual column names so people can help you with your query. Also, please include what you have tried so far. — kchason, Oct 30 '17 at 11:30
Two thoughts not directly related to the question: 1) You should be extremely wary of storing a plain-text password. It should ideally be hashed with something better than MD5 or SHA1, and salted. 2) There are several definitions of a week. See here: [WEEK()](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_week) and [YEARWEEK()](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_yearweek) — Wodin, Oct 30 '17 at 20:33
hi wodin this was just an example and i have actually hashed the password value. Thanks for suggestion though. — Suyash, Oct 31 '17 at 10:40

Wodin · Answer 1 · 2019-01-17T08:30:54.060

This is based on my answer to your other question:

Assuming week mode 3, since based on your question you seem to want weeks starting on a Monday. But perhaps you want mode 1, 5 or 7 instead (See WEEK()):

SELECT weeks.day, weeks.yearweek % 100 AS week, COUNT(DISTINCT ts.user_id) AS user_count
FROM (
    SELECT MIN(ADDDATE('1970-01-01', t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i)) AS day,
           YEARWEEK(ADDDATE('1970-01-01', t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i), 3) AS yearweek
    FROM (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS t0,
         (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS t1,
         (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS t2,
         (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS t3,
         (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS t4
    GROUP BY YEARWEEK(ADDDATE('1970-01-01', t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i), 3)
) AS weeks
LEFT OUTER JOIN tracked_search AS ts
    ON weeks.yearweek = YEARWEEK(ts.created, 3)
WHERE weeks.day >= '2017-05-01'
AND weeks.day < '2017-10-30'
GROUP BY weeks.yearweek;

Using the sample data in your question it returns the following:

+------------+------+------------+
| day        | week | user_count |
+------------+------+------------+
| 2017-05-01 |   18 |          0 |
| 2017-05-08 |   19 |          0 |
| 2017-05-15 |   20 |          0 |
| 2017-05-22 |   21 |          0 |
| 2017-05-29 |   22 |          0 |
| 2017-06-05 |   23 |          0 |
| 2017-06-12 |   24 |          0 |
| 2017-06-19 |   25 |          0 |
| 2017-06-26 |   26 |          0 |
| 2017-07-03 |   27 |          0 |
| 2017-07-10 |   28 |          0 |
| 2017-07-17 |   29 |          0 |
| 2017-07-24 |   30 |          0 |
| 2017-07-31 |   31 |          0 |
| 2017-08-07 |   32 |          0 |
| 2017-08-14 |   33 |          0 |
| 2017-08-21 |   34 |          0 |
| 2017-08-28 |   35 |          0 |
| 2017-09-04 |   36 |          0 |
| 2017-09-11 |   37 |          0 |
| 2017-09-18 |   38 |          0 |
| 2017-09-25 |   39 |          0 |
| 2017-10-02 |   40 |          0 |
| 2017-10-09 |   41 |          1 |
| 2017-10-16 |   42 |          2 |
| 2017-10-23 |   43 |          1 |
+------------+------+------------+

@ Wodin in the count part it shows count of only current date and not for the full week — Suyash, Oct 31 '17 at 10:38
@Suyash no, if you look at the output for week 42 (starting 2017-10-16) it shows 2. This is from your data where you had two records for user_id 1 (on the 18th and 22nd) and 2 records for user_id 2 (on the 16th and 17th). So there was only 1 record on the 16th, but the user_count correctly shows 2, because there were two separate users who downloaded something during week 42 according to your sample data. — Wodin, Oct 31 '17 at 14:26

score 0 · Answer 2 · answered Oct 31 '17 at 10:43

I was able to solve this using the following query. hope it will help someone

select 
t1.attempt_date,
coalesce(SUM(t1.attempt_count+t2.attempt_count), 0) AS attempt_count
from
(
  select DATE_FORMAT(a.Date,'%Y/%m/%d') as attempt_date,
  '0' as  attempt_count
  from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
  ) a
  where a.Date BETWEEN NOW() - INTERVAL 6 MONTH AND NOW()
  group by SUBDATE(a.Date, WEEKDAY(a.Date - INTERVAL 1 day)) 
)t1
left join
(
  select count(t1.user_count) as attempt_count, t1.created_date FROM (select distinct ts.user_id as user_count, date(ts.created) as created_date from tracked_search ts
inner join user_profile up ON up.id = ts.user_id
group by ts.created
order by ts.created) t1
Group by SUBDATE(date(t1.created_date), WEEKDAY(date(t1.created_date)))
)t2
on t2.created_date = t1.attempt_date
group by DAY(t1.attempt_date)
order by t1.attempt_date desc;

Why do you `inner join user_profile...`? You do not use it in the query. Also, your query returns an attempt_count of 4 for the test data in your question. Your question said you needed the `number of distinct user who made search for that week`. There are only two distinct users in your test data, so 4 is obviously wrong. Also, your query returns 0 for every other week, whereas the weeks starting with dates `2017-10-09` and `2017-10-23` should each show 1, since according to your test data there was a record for user 1 on the 15th and one record for user 2 on the 25th. — Wodin, Oct 31 '17 at 14:40
Suyash your `t2` subquery is returning records for `2017-10-15`, `2017-10-16` and `2017-10-25` using the test data in your question. The `left join` causes the ones for `2017-10-15` and `2017-10-25` to be ignored because they are not Mondays and the `t1` subquery only has Mondays. — Wodin, Oct 31 '17 at 14:56

MySQL query to get sum of user and group by week

2 Answers2

Linked