Skip to content

Instantly share code, notes, and snippets.

@serenamm

serenamm/slightly_more_complicated.py

Last active September 9, 2019 18:40
Show Gist options
  • Save serenamm/617a621703d4e9b30dc29c100f10a8e8 to your computer and use it in GitHub Desktop.
Save serenamm/617a621703d4e9b30dc29c100f10a8e8 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
slightly_more_complicated.py
SELECT
s.item_id_1,
s.item_id_2,
s.similarity_score
FROM (
SELECT
s.item_id_1,
s.item_id_2,
s.similarity_score,
ROW_NUMBER() OVER(PARTITION BY item_id_1 ORDER BY similarity_score DESC) as row_num
FROM product_similarity s
INNER JOIN products p
ON s.item_id_1 = p.item_id
INNER JOIN products q
ON s.item_id_2 = q.item_id
WHERE s.item_id_1 != s.item_id_2
AND p.category_id = q.category_id
)
WHERE row_num <= 10
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment