-- 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'));