123456789101112131415 |
- -- Contests by month
- SELECT strftime('%Y-%m', datetime(start_time / 1000, 'unixepoch')) as month, COUNT(*) as nb_contests FROM game GROUP BY strftime('%Y-%m', datetime(start_time / 1000, 'unixepoch'));
- -- Scores by month
- SELECT strftime('%Y-%m', datetime(created_at / 1000, 'unixepoch')) as month, COUNT(*) as nb_scores FROM score GROUP BY strftime('%Y-%m', datetime(created_at / 1000, 'unixepoch'));
- -- New users per month (only counting users with >5 games)
- select strftime('%Y-%m', datetime(created_at / 1000, 'unixepoch')), COUNT(*) as new_users
- from score as t1, user
- inner join rating on user.user_id = rating.user_id
- where total_scores > 5 and t1.user_id = user.user_id
- and not exists(
- select 1 from score as t2 where t1.created_at / 1000 < t2.created_at / 1000 AND t1.user_id = t2.user_id
- )
- GROUP BY strftime('%Y-%m', datetime(created_at / 1000, 'unixepoch'));
|