Sqlite gist

query.txt

/* events per day */
SELECT 
    DATE(timestamp / 10000000 - 62135596800, 'unixepoch') AS date, 
    (CASE 
        WHEN strftime('%w', DATE(timestamp / 10000000 - 62135596800, 'unixepoch')) == '0' THEN 'Sunday'
        WHEN strftime('%w', DATE(timestamp / 10000000 - 62135596800, 'unixepoch')) == '1' THEN 'Monday'
        WHEN strftime('%w', DATE(timestamp / 10000000 - 62135596800, 'unixepoch')) == '2' THEN 'Tuesday'
        WHEN strftime('%w', DATE(timestamp / 10000000 - 62135596800, 'unixepoch')) == '3' THEN 'Wednesday'
        WHEN strftime('%w', DATE(timestamp / 10000000 - 62135596800, 'unixepoch')) == '4' THEN 'Thursday'
        WHEN strftime('%w', DATE(timestamp / 10000000 - 62135596800, 'unixepoch')) == '5' THEN 'Friday'
        WHEN strftime('%w', DATE(timestamp / 10000000 - 62135596800, 'unixepoch')) == '6' THEN 'Saturday'
     END) as 'day of the week', 
    count(*) AS 'count of events'
FROM events 
GROUP BY date 
ORDER BY date DESC