Skip to content

Instantly share code, notes, and snippets.

@JorgeMichelena

JorgeMichelena/different_cases.sql Secret

Created September 19, 2022 21:37
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save JorgeMichelena/33d77db6821823ca14abf3818febf6af to your computer and use it in GitHub Desktop.
Save JorgeMichelena/33d77db6821823ca14abf3818febf6af to your computer and use it in GitHub Desktop.
Download ZIP
Raw
different_cases.sql
SELECT "targets_user"."id", "targets_user"."password", "targets_user"."last_login",
...
"targets_user"."date_of_birth", "targets_user"."zodiac_sign",
(COUNT(DISTINCT "targets_match"."id") + COUNT(DISTINCT T4."id")) AS "num_matches",
CASE WHEN ((COUNT(DISTINCT "targets_match"."id") + COUNT(DISTINCT T4."id")) > 0
AND (COUNT(DISTINCT "targets_match"."id") + COUNT(DISTINCT T4."id")) > 5
AND "targets_user"."zodiac_sign" = \'taurus\') THEN 15
WHEN ((COUNT(DISTINCT "targets_match"."id") + COUNT(DISTINCT T4."id")) > 0
AND (COUNT(DISTINCT "targets_match"."id") + COUNT(DISTINCT T4."id")) > 5) THEN 10
WHEN ((COUNT(DISTINCT "targets_match"."id") + COUNT(DISTINCT T4."id")) > 0
AND "targets_user"."zodiac_sign" = \'taurus\') THEN 10
WHEN (COUNT(DISTINCT "targets_match"."id") + COUNT(DISTINCT T4."id")) > 0 THEN 5
WHEN "targets_user"."zodiac_sign" = \'taurus\' THEN 5
ELSE 0 END AS "score"
FROM "targets_user" LEFT OUTER JOIN "targets_target" ON ("targets_user"."id" = "targets_target"."user_id")
LEFT OUTER JOIN "targets_match" ON ("targets_target"."id" = "targets_match"."target_1_id")
LEFT OUTER JOIN "targets_match" T4 ON ("targets_target"."id" = T4."target_2_id")
GROUP BY "targets_user"."id"
ORDER BY "score" DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment