Users who haven't voted in a poll

קהילה נתונים ודיווח
,
1

It can be very challenging to get your users to vote on a poll - especially if you want to catch your more casual visitors who only drop by once in a while.

To this end, I find that it is very useful to know who hasn’t yet voted in a poll. This enables me to easily chase them, without pestering those who have already voted.

I’ve refined this query to make it easy to use from the Topic/Post URL, as post.id is a real pain! And poll.name usually defaults to ‘poll’ if there is only one per post anyway. It includes email addresses, to enable direct emails if needed. But this could be easily stripped out.

-- [params]
-- group_id :group_id
-- int :topic_id
-- int :post_number = 1
-- string :poll_name = poll

WITH target_post AS (
  SELECT id AS post_id
  FROM posts
  WHERE topic_id = :topic_id
    AND post_number = :post_number
),

poll_check AS (
  SELECT COUNT(*) AS poll_count
  FROM polls p
  INNER JOIN target_post tp ON tp.post_id = p.post_id
  WHERE p.name = :poll_name
),

voters AS (
  SELECT DISTINCT pv.user_id AS voter_id
  FROM poll_votes pv
  INNER JOIN polls p ON p.id = pv.poll_id
  INNER JOIN target_post tp ON tp.post_id = p.post_id
  CROSS JOIN poll_check pc
  WHERE p.name = :poll_name
    AND 1 / pc.poll_count > 0  -- errors if poll_count = 0
)

SELECT
  u.id AS user_id,
  u.username,
  ue.email
FROM users u
INNER JOIN group_users gu ON gu.user_id = u.id
INNER JOIN groups g ON g.id = gu.group_id
INNER JOIN user_emails ue ON ue.user_id = u.id AND ue.primary = TRUE
CROSS JOIN poll_check pc
WHERE g.id = :group_id
  AND u.id > 0
  AND 1 / pc.poll_count > 0  -- errors if poll_count = 0
  AND NOT EXISTS (SELECT 1 FROM voters v WHERE v.voter_id = u.id)
ORDER BY u.username
לייק 1