How can I compare each single day of last two weeks in MySQL

Question

I'm stuck, this job seems too complicated for me I hope in some goodness help here!

So this is my table views:

+---+--------------------+
| id|                time|
+---+--------------------+
| 1 | 2016-10-06 10:13:11|
| 2 | 2016-10-07 15:33:31|
| 3 | 2016-10-08 20:13:13|
| 4 | 2016-10-09 09:16:21|
| 5 | 2016-10-10 18:23:17|
| . |                 ...|
+---+--------------------+

I need to select in array the rows count of each day for the last 2 weeks and compare them (so I need to compare monday of current week and previous week).

So I tried:

SELECT 'last_week' AS week, COUNT(*) AS rows 
    FROM `views`
    WHERE `time` >= CURDATE() - INTERVAL  6 DAY 
      AND `time` < CURDATE() + INTERVAL  1 DAY
  UNION ALL
    SELECT 'previous_week' AS week, COUNT(*) AS rows 
    FROM `views`
    WHERE `time` >= CURDATE() - INTERVAL 13 DAY
      AND `time` < CURDATE() - INTERVAL  6 DAY;

But it counts rows of each weeks and not single days. Yes, I can add eg. AND DAYOFWEEK(time) = 7; to the query, but in this case I have to declared it for seven times.

Basically I need something like:

+---------------+--------------------+
|          week | 1| 2| 3| 4| 5| 6| 7|
+---------------+--+--+--+--+--+--+--+
|     last_week | 1| 5| 3|15|12| 1| 3|
| previous_week |10|15|34|22|55|78|99|
+---------------+--+--+--+--+--+--+--+

Where 1 is Monday, 7 Sunday.

Answer 1

PRELIMINARY DATE MATH

Here are two expressions you need to understand

mysql> SET @this_past_monday = CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND;
Query OK, 0 rows affected (0.01 sec)

mysql> SET @prev_two_mondays = @this_monday - INTERVAL 2 WEEK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CURDATE(),@this_past_monday,@prev_two_mondays;
+------------+---------------------+---------------------+
| CURDATE()  | @this_past_monday   | @prev_two_mondays   |
+------------+---------------------+---------------------+
| 2016-10-11 | 2016-10-10 00:00:00 | 2016-09-26 00:00:00 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>

GOAL OF THIS ANSWER

You need to combine two queries

• generate a temp table with the dates based on these two Mondays
• retrieve counts from views table based on these two Mondays

QUERY #1

Generate all dates from Jan 1st last year to 1000 days later, extracting all dates spanning 2 weeks before this past Monday

SELECT dt FROM
(SELECT MAKEDATE(YEAR(NOW()) - 1,H*100+T*10+U) dt FROM
(SELECT 0 H UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) H,
(SELECT 0 T UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T,
(SELECT 0 U UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) U
ORDER BY dt) A
WHERE dt >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND - INTERVAL 2 WEEK
AND   dt <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND;

QUERY #1 EXECUTED

mysql> SELECT dt FROM
    -> (SELECT MAKEDATE(YEAR(NOW()) - 1,H*100+T*10+U) dt FROM
    -> (SELECT 0 H UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) H,
    -> (SELECT 0 T UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T,
    -> (SELECT 0 U UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) U
    -> ORDER BY dt) A
    -> WHERE dt >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND  - INTERVAL 2 WEEK
    -> AND   dt <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND;
+------------+
| dt         |
+------------+
| 2016-09-26 |
| 2016-09-27 |
| 2016-09-28 |
| 2016-09-29 |
| 2016-09-30 |
| 2016-10-01 |
| 2016-10-02 |
| 2016-10-03 |
| 2016-10-04 |
| 2016-10-05 |
| 2016-10-06 |
| 2016-10-07 |
| 2016-10-08 |
| 2016-10-09 |
+------------+
14 rows in set (0.00 sec)

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2016-10-11 |
+------------+
1 row in set (0.00 sec)

mysql>

QUERY #2

SELECT DATE(`time`) dt,COUNT(1) RowCount FROM views
WHERE `time` >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND - INTERVAL 2 WEEK
AND   `time` <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND
GROUP BY DATE(`time`);

COMBINATION

LEFT JOIN Query #1 and Query #2

SELECT A.dt,IFNULL(B.RowCount,0) RowsCounted FROM
(SELECT dt FROM
(SELECT MAKEDATE(YEAR(NOW()) - 1,H*100+T*10+U) dt FROM
(SELECT 0 H UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) H,
(SELECT 0 T UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T,
(SELECT 0 U UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) U
ORDER BY dt) A
WHERE dt >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND  - INTERVAL 2 WEEK
AND   dt <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND) A
LEFT JOIN
(SELECT DATE(`time`) dt,COUNT(1) RowCount FROM views
WHERE `time` >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND - INTERVAL 2 WEEK
AND   `time` <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND
GROUP BY DATE(`time`)) B
USING (dt);

REASON FOR COMBINATION / LEFT JOIN

In the event there are no rows for a particular date, you want Query #1 to still show a date but present 0 as the number of rows

GIVE IT A TRY !!!

NOTE : I will leave the gory details of presenting the data in your capable hands