artemisdart

Sometimes I tinker around with the SQL in my little relational database to see if I can solve different problems. The other night, it occurred to me that it would be nice to know what calendar date I hit different milestones in my kudos counts -- 1,000, 2,000, and so on.

After an hour or so of messing around with SQL, I got this to work -- but only for one total at a time. So, if I want to know the different dates I hit multiple milestones, I will have to run it separately, one for each. Curses!

Still, this is pretty cool, and way better than anything AO3 has to offer in its reporting.

Here's the snippet:

-- Cumulative sums using nested subqueries
WITH "Pool" AS (
WITH "Cumulative" AS (
   SELECT
    "K1"."Kudos_date",
    SUM("K2"."Kudos_count") AS "Cumulative_Kudos"
   FROM "Fact_Kudos" AS "K1"
    INNER JOIN "Fact_Kudos" AS "K2"
   ON "K1"."Kudos_date" >= "K2"."Kudos_date"
   GROUP BY
     "K1"."Kudos_date"
   ORDER BY
     "K1"."Kudos_date"
   )
  SELECT "Cumulative"."Kudos_date",
  (SELECT SUM("K2"."Kudos_count")
     FROM "Fact_Kudos" AS "K2"
     WHERE "Cumulative"."Kudos_date" >= "K2"."Kudos_date") AS "cumulative_consumption"
  FROM
     "Cumulative"
  ORDER BY
     "Cumulative"."Kudos_date"
)
SELECT MIN("Pool"."Kudos_date")
FROM
   "Pool"
WHERE
   "Pool"."cumulative_consumption" >= '6000'
GROUP BY
   "Pool"."Kudos_date"
ORDER BY
   "Pool"."Kudos_date"
LIMIT 1;

And here are my dates!