Skip to content

Instantly share code, notes, and snippets.

@rileyjshaw

rileyjshaw/instructions.sql

Created December 12, 2019 21:19
Show Gist options
  • Save rileyjshaw/38822d4e5c6a8b52237eb1021d7a10b9 to your computer and use it in GitHub Desktop.
Save rileyjshaw/38822d4e5c6a8b52237eb1021d7a10b9 to your computer and use it in GitHub Desktop.
Download ZIP
Weekly / overall winner queries for Mozilla's 2019 Open Voice Challenge Pilot (rough draft)
Raw
instructions.sql
-- To connect to the prod database with write access,
-- $ ssh voice-mysql-prod
-- $ sudo su
-- $ mysql
-- > use voice;
-- > ...
/*
Example team award:
INSERT INTO earn (achievement_id, team_id) VALUES (9, 1);
INSERT INTO earn (achievement_id, team_id) VALUES (10, 4);
INSERT INTO earn (achievement_id, team_id) VALUES (11, 3);
*/
Raw
overall_individual_top_contributor_per_team.sql
SELECT *
FROM (SELECT email,
team,
( bonus + clip_points + vote_points ) AS total_points
FROM (SELECT email,
team,
bonus,
clip_points,
Count(votes.id) AS vote_points
FROM (SELECT email,
challenger.client_id,
team,
start_date,
end_date,
bonus,
Count(clips.id) AS clip_points
FROM (SELECT user_clients.client_id,
email,
teams.NAME
AS
team,
challenges.start_date
AS
start_date,
Timestampadd(week, 3,
challenges.start_date) AS
end_date
,
COALESCE(Sum(achievements.points), 0) AS bonus
FROM user_clients
INNER JOIN enroll
ON user_clients.client_id = enroll.client_id
INNER JOIN challenges
ON enroll.challenge_id = challenges.id
INNER JOIN teams
ON enroll.team_id = teams.id
AND challenges.id = teams.challenge_id
LEFT JOIN earn
ON user_clients.client_id = earn.client_id
AND earn.team_id IS NULL
AND earn.earned_at BETWEEN challenges.start_date AND
Timestampadd(week, 3, challenges.start_date)
INNER JOIN achievements
ON earn.achievement_id = achievements.id
AND challenges.id = achievements.challenge_id
AND challenges.url_token = "pilot"
GROUP BY user_clients.client_id,
email,
start_date,
end_date) challenger
LEFT JOIN clips
ON challenger.client_id = clips.client_id
AND clips.created_at BETWEEN start_date AND end_date
GROUP BY challenger.client_id,
start_date,
end_date,
bonus) speaker
LEFT JOIN votes
ON speaker.client_id = votes.client_id
AND votes.created_at BETWEEN start_date AND end_date
GROUP BY speaker.client_id,
speaker.bonus,
speaker.clip_points) voter
ORDER BY total_points DESC) AS final
GROUP BY team
ORDER BY total_points DESC;
Raw
overall_weekly_challenge_team_winners.sql
SELECT teams.NAME,
Sum(achievements.points) AS weekly_challenge_points
FROM earn
INNER JOIN achievements
ON earn.achievement_id = achievements.id
INNER JOIN teams
ON teams.id = earn.team_id
INNER JOIN challenges
ON team.challenge_id = challenges.id
WHERE challenges.url_token = "pilot"
GROUP BY teams.NAME
ORDER BY weekly_challenge_points DESC;
Raw
week_1_individual_first_contributors.sql
SELECT email,
clip_points,
vote_points
FROM (SELECT email,
clip_points,
Count(votes.id) AS vote_points
FROM (SELECT challenger.client_id,
challenger.email,
start_date,
end_date,
Count(clips.id) AS clip_points
FROM (SELECT user_clients.client_id,
email,
challenges.start_date AS
start_date,
-- HACK: Adjust until 3 results are returned. Seriously simplifies
-- the query while we're finding winners manually, but a
-- smarter query is in progress below.
Timestampadd(hour, 28, challenges.start_date) AS
end_date
FROM user_clients
LEFT JOIN enroll
ON user_clients.client_id =
enroll.client_id
LEFT JOIN challenges
ON enroll.challenge_id = challenges.id
WHERE challenges.url_token = "pilot"
GROUP BY user_clients.client_id,
email,
start_date,
end_date) challenger
LEFT JOIN clips
ON challenger.client_id = clips.client_id
AND clips.created_at BETWEEN
start_date AND end_date
GROUP BY challenger.client_id,
start_date,
end_date) speaker
LEFT JOIN votes
ON speaker.client_id = votes.client_id
AND votes.created_at BETWEEN start_date AND end_date
GROUP BY speaker.client_id,
speaker.clip_points) voter
WHERE clip_points >= 100
AND vote_points >= 200;
/*
-- In progress: smarter week 1 winners of the INDIVIDUAL FIRST CONTRIBUTOR CHALLENGE:
SELECT email,
Greatest(clip_moment, vote_points) AS moment_achieved
FROM (SELECT email,
clip_points,
Count(votes.id) AS vote_points
FROM (SELECT challenger.client_id,
challenger.email,
start_date,
end_date,
Nth_value(clips.created_at, 200)
OVER (
ORDER BY clips.created_at ASC ) second_highest_salary
FROM (SELECT user_clients.client_id,
email,
challenges.start_date AS
start_date,
Timestampadd(week, 1, challenges.start_date) AS
end_date
FROM user_clients
LEFT JOIN enroll
ON user_clients.client_id =
enroll.client_id
LEFT JOIN challenges
ON enroll.challenge_id = challenges.id
WHERE challenges.url_token = "pilot"
GROUP BY user_clients.client_id,
email,
start_date,
end_date) challenger
LEFT JOIN clips
ON challenger.client_id = clips.client_id
AND clips.created_at BETWEEN
start_date AND end_date
GROUP BY challenger.client_id,
start_date,
end_date) speaker
LEFT JOIN votes
ON speaker.client_id = votes.client_id
AND votes.created_at BETWEEN start_date AND end_date
GROUP BY speaker.client_id,
speaker.clip_points) voter
WHERE clip_points >= 100
AND vote_points >= 200
ORDER BY moment_achieved ASC; -- Earliest date that both conditions were met.
-- LIMIT 3;
*/
Raw
week_1_individual_top_contributors.sql
SELECT email,
( bonus + clip_points + vote_points ) AS total_points
FROM (SELECT email,
bonus,
clip_points,
Count(votes.id) AS vote_points
FROM (SELECT email,
challenger.client_id,
start_date,
end_date,
bonus,
Count(clips.id) AS clip_points
FROM (SELECT user_clients.client_id,
email,
challenges.start_date AS
start_date,
Timestampadd(week, 1, challenges.start_date) AS
end_date
,
Coalesce(Sum(achievements.points), 0) AS bonus
FROM user_clients
INNER JOIN enroll
ON user_clients.client_id = enroll.client_id
INNER JOIN challenges
ON enroll.challenge_id = challenges.id
INNER JOIN teams
ON enroll.team_id = teams.id
AND challenges.id = teams.challenge_id
LEFT JOIN earn
ON user_clients.client_id = earn.client_id
AND earn.team_id IS NULL
AND earn.earned_at BETWEEN challenges.start_date AND
Timestampadd(week, 1, challenges.start_date)
INNER JOIN achievements
ON earn.achievement_id = achievements.id
AND challenges.id = achievements.challenge_id
AND challenges.url_token = "pilot"
GROUP BY user_clients.client_id,
email,
start_date,
end_date) challenger
LEFT JOIN clips
ON challenger.client_id = clips.client_id
AND clips.created_at BETWEEN start_date AND end_date
GROUP BY challenger.client_id,
start_date,
end_date,
bonus) speaker
LEFT JOIN votes
ON speaker.client_id = votes.client_id
AND votes.created_at BETWEEN start_date AND end_date
GROUP BY speaker.client_id,
speaker.bonus,
speaker.clip_points) voter
ORDER BY total_points DESC
LIMIT 3;
Raw
week_1_team_challenge_winners.sql
SELECT teams.NAME,
Count(enroll.id)
FROM enroll
INNER JOIN challenges
ON enroll.challenge_id = challenges.id
INNER JOIN teams
ON enroll.team_id = teams.id
WHERE challenges.url_token = "pilot"
AND enroll.enrolled_at BETWEEN challenges.start_date AND
Timestampadd(week, 1,
challenges.start_date)
GROUP BY teams.id
ORDER BY Count(enroll.id) DESC;
Raw
week_2_individual_contribute_after_bonus_email.sql
SELECT user_clients.email email,
contribution_type,
contribution.created_at timestamp_utc
FROM user_clients
INNER JOIN enroll
ON user_clients.client_id = enroll.client_id
INNER JOIN (SELECT contribution_type,
created_at,
client_id
FROM (SELECT 'Clip' contribution_type,
created_at,
client_id
FROM clips
UNION ALL
SELECT 'Vote' contribution_type,
created_at,
client_id
FROM votes) subquery
WHERE created_at > '2019-11-25 23:09') contribution
ON user_clients.client_id = contribution.client_id
INNER JOIN challenges
ON enroll.challenge_id = challenges.id
WHERE challenges.url_token = "pilot"
GROUP BY user_clients.email
ORDER BY contribution.created_at ASC
LIMIT 3;
Raw
week_2_individual_most_invites.sql
SELECT user_clients.email,
invite_count
FROM user_clients
INNER JOIN enroll
ON user_clients.client_id = enroll.client_id
INNER JOIN (SELECT enroll.invited_by AS inviter_id,
Count(enroll.invited_by) AS invite_count
FROM enroll
INNER JOIN challenges
ON challenges.id = enroll.challenge_id
WHERE challenges.url_token = "pilot"
AND enroll.enrolled_at BETWEEN
challenges.start_date AND
Timestampadd(week, 2,
challenges.start_date)
GROUP BY enroll.invited_by) AS invitees
ON inviter_id = enroll.url_token
ORDER BY invite_count DESC
LIMIT 3;
Raw
week_2_individual_top_contributors_per_team.sql
SELECT *
FROM (SELECT email,
team,
( bonus + clip_points + vote_points ) AS total_points
FROM (SELECT email,
team,
bonus,
clip_points,
Count(votes.id) AS vote_points
FROM (SELECT email,
challenger.client_id,
team,
start_date,
end_date,
bonus,
Count(clips.id) AS clip_points
FROM (SELECT user_clients.client_id,
email,
teams.NAME
AS
team,
challenges.start_date
AS
start_date,
Timestampadd(week, 2,
challenges.start_date) AS
end_date
,
COALESCE(Sum(achievements.points), 0) AS bonus
FROM user_clients
INNER JOIN enroll
ON user_clients.client_id = enroll.client_id
INNER JOIN challenges
ON enroll.challenge_id = challenges.id
INNER JOIN teams
ON enroll.team_id = teams.id
AND challenges.id = teams.challenge_id
LEFT JOIN earn
ON user_clients.client_id = earn.client_id
AND earn.team_id IS NULL
AND earn.earned_at BETWEEN challenges.start_date AND
Timestampadd(week, 2, challenges.start_date)
INNER JOIN achievements
ON earn.achievement_id = achievements.id
AND challenges.id = achievements.challenge_id
AND challenges.url_token = "pilot"
GROUP BY user_clients.client_id,
email,
start_date,
end_date) challenger
LEFT JOIN clips
ON challenger.client_id = clips.client_id
AND clips.created_at BETWEEN start_date AND end_date
GROUP BY challenger.client_id,
start_date,
end_date,
bonus) speaker
LEFT JOIN votes
ON speaker.client_id = votes.client_id
AND votes.created_at BETWEEN start_date AND end_date
GROUP BY speaker.client_id,
speaker.bonus,
speaker.clip_points) voter
ORDER BY total_points DESC) AS final
GROUP BY team
ORDER BY total_points DESC;
Raw
week_2_team_challenge_winners.sql
SELECT teams.NAME,
Count(enroll.id)
FROM enroll
INNER JOIN challenges
ON enroll.challenge_id = challenges.id
INNER JOIN teams
ON enroll.team_id = teams.id
WHERE challenges.url_token = "pilot"
AND enroll.enrolled_at BETWEEN challenges.start_date AND
Timestampadd(week, 2,
challenges.start_date)
AND enroll.invited_by IS NOT NULL
GROUP BY teams.id
ORDER BY Count(enroll.id) DESC;
Raw
week_3_individual_early_bird.sql
SELECT user_clients.email,
contribution.created_at AS contribution_time
FROM user_clients
INNER JOIN enroll
ON user_clients.client_id = enroll.client_id
INNER JOIN challenges
ON enroll.challenge_id = challenge_id
INNER JOIN custom_goals
ON user_clients.client_id = custom_goals.client_id
INNER JOIN (SELECT created_at,
client_id
FROM (SELECT created_at,
client_id
FROM clips
UNION ALL
SELECT created_at,
client_id
FROM votes) subquery) contribution
ON user_clients.client_id = contribution.client_id
AND contribution.created_at BETWEEN Timestampadd(week, 2,
challenges.start_date)
AND
Timestampadd(week, 3,
challenges.start_date)
AND contribution.created_at > custom_goals.created_at
WHERE challenges.url_token = "pilot"
ORDER BY contribution.created_at
LIMIT 1;
Raw
week_3_individual_first_to_set_goals.sql
SELECT custom_goals.created_at,
user_clients.email
FROM custom_goals
INNER JOIN user_clients
ON custom_goals.client_id = user_clients.client_id
INNER JOIN enroll
ON enroll.client_id = user_clients.client_id
AND custom_goals.created_at >= enroll.enrolled_at
INNER JOIN challenges
ON enroll.challenge_id = challenges.id
AND custom_goals.created_at >= Timestampadd(week, 2,
challenges.start_date)
WHERE challenges.url_token = "pilot"
ORDER BY custom_goals.created_at
LIMIT 3;
Raw
week_3_individual_highest_accuracy.sql
SELECT email,
Round(100 * valid / validated, 2) AS rate,
validated
FROM (SELECT user_clients.*,
Coalesce(Sum(upvotes >= 2
AND upvotes > downvotes), 0) AS valid,
Coalesce(Sum(( upvotes >= 2
OR downvotes >= 2 )
AND upvotes <> downvotes), 0) AS validated
FROM (SELECT user_clients.*,
Sum(votes.is_valid) AS upvotes,
Sum( ! votes.is_valid) AS downvotes
FROM user_clients
INNER JOIN enroll
ON enroll.client_id = user_clients.client_id
INNER JOIN challenges
ON enroll.challenge_id = challenges.id
LEFT JOIN clips
ON user_clients.client_id = clips.client_id
AND clips.created_at BETWEEN
Timestampadd(week, 2,
challenges.start_date) AND
Timestampadd(week, 3,
challenges.start_date)
LEFT JOIN votes
ON clips.id = votes.clip_id
WHERE challenges.url_token = "pilot"
GROUP BY user_clients.client_id,
clips.id) user_clients
GROUP BY user_clients.client_id) t
ORDER BY rate DESC,
validated DESC
LIMIT 5;
Raw
week_3_individual_midnight_bus.sql
SELECT user_clients.email,
contribution.created_at AS contribution_time
FROM user_clients
INNER JOIN enroll
ON user_clients.client_id = enroll.client_id
INNER JOIN challenges
ON enroll.challenge_id = challenge_id
INNER JOIN custom_goals
ON user_clients.client_id = custom_goals.client_id
INNER JOIN (SELECT created_at,
client_id
FROM (SELECT created_at,
client_id
FROM clips
UNION ALL
SELECT created_at,
client_id
FROM votes) subquery) contribution
ON user_clients.client_id = contribution.client_id
AND contribution.created_at BETWEEN Timestampadd(week, 1,
challenges.start_date)
AND
Timestampadd(week, 2,
challenges.start_date)
AND contribution.created_at > custom_goals.created_at
WHERE challenges.url_token = "pilot"
ORDER BY contribution.created_at DESC
LIMIT 1;
Raw
week_3_team_challenge_winners_highest_average_contribution.sql
-- Buggy, unused and untested.
SELECT teams.NAME
team_name,
Sum(contributions.contribution_count) / Count(user_clients.client_id) AS
week_3_contributions
FROM teams
INNER JOIN enroll
ON teams.id = enroll.team_id
INNER JOIN user_clients
ON enroll.client_id = user_clients.client_id
INNER JOIN challenges
ON enroll.challenge_id = challenges.id
LEFT OUTER JOIN (SELECT user_clients.client_id,
Count(*) AS contribution_count
FROM user_clients
INNER JOIN enroll
ON user_clients.client_id =
enroll.client_id
INNER JOIN challenges
ON enroll.challenge_id = challenge_id
INNER JOIN (SELECT created_at,
client_id
FROM (SELECT created_at,
client_id
FROM clips
UNION ALL
SELECT created_at,
client_id
FROM votes) subquery)
contribution
ON user_clients.client_id =
contribution.client_id
AND contribution.created_at BETWEEN
Timestampadd(week, 2,
challenges.start_date) AND
Timestampadd(week, 3,
challenges.start_date)
WHERE challenges.url_token = "pilot"
GROUP BY user_clients.client_id) contributions
ON user_clients.client_id = contributions.client_id
WHERE challenges.url_token = "pilot"
GROUP BY teams.id
ORDER BY week_3_contributions DESC;
Raw
week_3_team_challenge_winners_highest_submission_accuracy.sql
SELECT team_name,
Round(100 * valid / validated, 2) AS rate
FROM (SELECT team_name,
COALESCE(Sum(upvotes >= 2
AND upvotes > downvotes), 0) AS valid,
COALESCE(Sum(( upvotes >= 2
OR downvotes >= 2 )
AND upvotes <> downvotes), 0) AS validated
FROM (SELECT teams.NAME AS team_name,
teams.id,
Sum(votes.is_valid) AS upvotes,
Sum( ! votes.is_valid) AS downvotes
FROM user_clients
INNER JOIN enroll
ON enroll.client_id = user_clients.client_id
INNER JOIN challenges
ON enroll.challenge_id = challenges.id
INNER JOIN teams
ON enroll.team_id = teams.id
LEFT JOIN clips
ON user_clients.client_id = clips.client_id
AND clips.created_at BETWEEN
Timestampadd(week, 2,
challenges.start_date) AND
Timestampadd(week, 3,
challenges.start_date)
LEFT JOIN votes
ON clips.id = votes.clip_id
WHERE challenges.url_token = "pilot"
GROUP BY teams.id,
clips.id) teams
GROUP BY teams.id) t
ORDER BY rate DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment