Skip to content

Instantly share code, notes, and snippets.

@ollieh-m
Created June 16, 2022 16:01
Show Gist options
  • Save ollieh-m/70c5e469d32c28709c300276781dc486 to your computer and use it in GitHub Desktop.
Save ollieh-m/70c5e469d32c28709c300276781dc486 to your computer and use it in GitHub Desktop.
SELECT dwelled_recipe_id as recipe_id, query, query_rank
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY dwelled_recipe_id ORDER BY session_count desc) as query_rank
FROM (
SELECT dwelled_recipe_id, query, count(*) as session_count
FROM global_dwh.search_session_stats
WHERE region = 'ES' AND dwelled_recipe_id IS NOT NULL AND started_at > '2022-01-01 00:00:00.000+00'
GROUP BY 1, 2
) session_count_summary
) ranked_session_count_summary
WHERE query_rank < 6
ORDER BY dwelled_recipe_id, query_rank
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment