artemisdart

On the Discord channel, someone was wanting some Gingerpilot fluff. I wrote a quick query to find all my Gingerpilot fics that this user hasn't kudosed yet, and also screening out "Termination Dust," because it's super depressing and definitely NOT fluff.

Here's the SQL! Right now it yields 22 hits, meaning they've kudosed 8 of my Gingerpilot fics. :)

WITH "Kudos" AS (
SELECT DISTINCT
   "Work"."Work_key",
   "Work"."Work_name",
   "Work"."Work_rating"
FROM "Dim_Work" AS "Work"
INNER JOIN "Fact_Kudos" AS "Kudos"
   ON "Work"."Work_key" = "Kudos"."Kudos_work"
WHERE "Work"."Work_ship" = 'Gingerpilot'
AND "Work"."Work_name" <> 'Termination Dust'
   ), "SSS" AS
   (SELECT
      "Work"."Work_key",
      "Work"."Work_name",
      "Work"."Work_rating"
   FROM "Fact_Kudos" AS "Fact"
   INNER JOIN "Dim_Work" AS "Work"
      ON "Fact"."Kudos_work" = "Work"."Work_key"
   WHERE "Work"."Work_ship" = 'Gingerpilot'
   AND "Fact"."Kudos_user" = '2762'
   )
SELECT * FROM "Kudos"
EXCEPT
SELECT * FROM "SSS"

And here's the SQL that finds all the Gingerpilot fics this person has kudosed:

SELECT COUNT ("Work"."Work_key")
FROM "Dim_Work" AS "Work"
INNER JOIN "Fact_Kudos" AS "Kudos"
ON "Kudos"."Kudos_work" = "Work"."Work_key"
AND "Kudos"."Kudos_user" = '2762'
AND "Work"."Work_ship" = 'Gingerpilot'