Skip to content

Instantly share code, notes, and snippets.

@finbarr
Last active Aug 14, 2018
Embed
What would you like to do?
Query to find the average number of reviews before a negative review.
WITH ranked_reviews AS (
SELECT
a.name AS app_name,
a.id AS app_id,
RANK() OVER (
PARTITION BY r.app_id
ORDER BY
r.posted_at ASC
) AS rank,
r.score,
r.posted_at
FROM
apps a
INNER JOIN reviews r ON r.app_id = a.id
)
SELECT
AVG(min_bad_review_rank)
FROM
(
SELECT
MIN(rank) AS min_bad_review_rank,
rr.app_id
FROM
ranked_reviews rr
WHERE
EXISTS(
SELECT
1
FROM
ranked_reviews rr2
WHERE
rr2.app_id = rr.app_id
AND rr2.rank >= 100
)
AND score < 3
GROUP BY
rr.app_id
) AS q1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment