×

Welcome to TagMyCode

Please login or create account to add a snippet.
 

Past 7 Days

0
0
 
0
Language: SQL
Posted by: Rick Hays
Added: May 27, 2020 6:24 PM
Modified: Oct 23, 2020 3:26 PM
Views: 4256
Tags: mysql
  1. #############################################################
  2. SELECT     DS.TheDay AS TheDate, COALESCE(COUNT(id), 0) AS TheCount
  3. FROM       page_views PV
  4. RIGHT JOIN (SELECT curdate()                           AS TheDay  UNION
  5.             SELECT DATE_SUB(curdate(), INTERVAL 1 DAY) AS TheDay  UNION
  6.             SELECT DATE_SUB(curdate(), INTERVAL 2 DAY) AS TheDay  UNION
  7.             SELECT DATE_SUB(curdate(), INTERVAL 3 DAY) AS TheDay  UNION
  8.             SELECT DATE_SUB(curdate(), INTERVAL 4 DAY) AS TheDay  UNION
  9.             SELECT DATE_SUB(curdate(), INTERVAL 5 DAY) AS TheDay  UNION
  10.             SELECT DATE_SUB(curdate(), INTERVAL 6 DAY) AS TheDay) AS DS
  11. ON          DATE(PV.viewed_at) = DS.TheDay
  12. AND         PV.viewed_at > DATE_SUB(curdate(), INTERVAL 7 DAY)
  13. GROUP BY    TheDate
  14. ORDER BY    DS.TheDay;
  15. #############################################################
  16.  
  17.  

Write a comment

To post comments, please log in or register.