Testing poll votes

Testing
1

Vote or die!!!

  • cat
  • dog
  • pigs
  • eagle
  • turtle
  • whales
0 voters
4
Python or R?
  • Python
  • R
0 voters
8

–[params]
– string :query_interval = 1 week
– date :start_date
– date :end_date

WITH query_periods AS (
SELECT
generate_series(:start_date, :end_date, :query_interval::interval)::date AS period_start,
(generate_series(:start_date, :end_date, :query_interval::interval)::date + :query_interval::interval - INTERVAL ‘1 DAY’)::date AS period_end
),
poll_votes_with_topics AS (
SELECT
pv.user_id,
po.poll_id,
pl.post_id,
p.topic_id
FROM poll_votes pv
JOIN poll_options po ON po.id = pv.poll_option_id
JOIN polls pl ON pl.id = po.poll_id
JOIN posts p ON p.id = pl.post_id
WHERE pv.created_at >= (SELECT MIN(period_start) FROM query_periods)
AND pv.created_at <= (SELECT MAX(period_end) FROM query_periods)
),
users_who_posted AS (
SELECT DISTINCT
posts.user_id,
posts.topic_id
FROM posts
WHERE posts.created_at >= (SELECT MIN(period_start) FROM query_periods)
AND posts.created_at <= (SELECT MAX(period_end) FROM query_periods)
),
final AS (
SELECT
u.username,
ue.email,
t.title
FROM poll_votes_with_topics pvt
JOIN users u ON u.id = pvt.user_id
LEFT JOIN users_who_posted uwp
ON uwp.user_id = pvt.user_id AND uwp.topic_id = pvt.topic_id
LEFT JOIN user_emails ue ON ue.user_id = u.id AND ue.primary = true
JOIN topics t ON t.id = pvt.topic_id
WHERE uwp.user_id IS NULL
)

SELECT DISTINCT username, email, title
FROM final
ORDER BY username;