Users who started with a identification request and then made another post (self-answers excluded)

0

Please login or register to vote for this query.

(click on this box to dismiss)

Movies and TV

Q&A for movie and tv enthusiasts

WITH
  first_user_posts AS (
    SELECT
      u.id AS userId,
      MIN(p.id) AS postId
    FROM users u 
    JOIN posts p ON (u.id = p.ownerUserId)
    WHERE p.posttypeid IN (1, 2)
    GROUP BY u.id
  ),
  first_ident AS (
    SELECT DISTINCT p.*
    FROM first_user_posts fup
    JOIN posts p ON (p.id = fup.postId)
    JOIN posttags pt ON (p.id = pt.postid)
    JOIN tags t ON (pt.tagid = t.id)
    WHERE p.posttypeid = 1 -- questions
      AND t.tagname LIKE 'identify%' -- identification requests
  )
SELECT
  COUNT(DISTINCT fi.owneruserid) AS nrs,
  CASE
    WHEN op.id IS NOT NULL THEN 1
    ELSE 0
  END AS has_other_post
FROM first_ident fi
LEFT JOIN posts op
  ON (   fi.id <> op.id
     AND (fi.id <> op.parentid OR op.parentid IS NULL)
     AND fi.owneruserid = op.owneruserid
     AND op.posttypeid IN (1,2))
GROUP BY
  CASE
    WHEN op.id IS NOT NULL THEN 1
    ELSE 0
  END;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results