artemisdart

The recent work I did on my database means I can now run this query to find any gift recipients I wrote for who never left a kudos on my gift for them.

SELECT
   r."Recipient_user_key",
   u."User_name",
   w."Work_key",
   w."Work_name"
FROM "Bridge_WorkRecipient" r
JOIN "Dim_Work" w
   ON r."Work_key" = w."Work_key"
JOIN "Dim_User" u
   ON r."Recipient_user_key" = u."User_key"
LEFT JOIN "Fact_Kudos" k
   ON k."Kudos_work" = w."Work_key"
   AND k."Kudos_user" = r."Recipient_user_key"
WHERE w."Work_is_gift" = TRUE
   AND k."Kudos_key" IS NULL;

There are 11 such cases!

Current Mood: chipper

Today it occurred to me to wonder what is the average number of days between posting chapters of my various fics. I whipped up some SQL to go against my relational database, and found the answer -- just 3.63 days! Wow, I post a lot!

Here's the SQL:

SELECT
   AVG("t"."next_chapter_date" - "t"."Chapter_date") AS avg_days_between
FROM (
   SELECT DISTINCT
      "Fact_Chapter"."Chapter_date"::date,
      LEAD("Fact_Chapter"."Chapter_date"::date) OVER (ORDER BY "Fact_Chapter"."Chapter_date") AS next_chapter_date
   FROM "Fact_Chapter"
   WHERE "Fact_Chapter"."Chapter_date" > '2023-02-01'
      AND "Fact_Chapter"."Chapter_date" <= now()
   ORDER BY "Fact_Chapter"."Chapter_date"
) AS "t"
WHERE "t"."Chapter_date" <> "t"."next_chapter_date"
   AND "t"."next_chapter_date" IS NOT NULL;

I needed to limit the dates in the subquery to start in February 2023, when I started posting "for real." (Later, I posted some backdated fics from YEARS earlier, but they were messing up the average.)

I also needed to limit the dates to only things posted before "now," for the same reason -- because I have a bunch of words dated to 12/31/9999, which really mess with the average if they're included.

One final refinement to fit my use case! I had multiple chapters of fics all posted on the same day, which was really bringing down my average. So I ruled out instances where the Chapter_date was equal to the next_chapter_date, and ran it without that.

Current Mood: cheerful

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'

Current Mood: bouncy

I stayed up late to get my relational database up to date. Then I ran a couple of queries to verify for myself that "A Helping Hand" (my Murderbot/Dr. Gurathin fic) really is as hot as I think it is, kudos-wise.

It is! A Helping Hand is currently in second place when it comes to the speed with which it gathered kudos in its first few days. Behold!

#1: What Happens on Peridea, with 115 kudos in its first 3 days (WOWWWW). I stayed up late one night after watching an episode of Ahsoka and knocked this out before 3 AM, and it was quiiiiiite popular. Shin Hati / Sabine Wren shippers are fierce!

#2: A Helping Hand, at 91 kudos in its first 3 days. I finished this at 3 AM the other night and posted without editing, haha.

#3: Bloodlines, with 81 kudos in its first 3 days. I wrote this late one night and posted it around 3 AM, and then it took off big-time immediately. You see, the movie Nimona was trending on Netflix at the time, and there were very few fics for Ambrosius / Ballister. So everyone clicked on mine and left a kudos, despite the fact that I had only posted a chapter or three at the time. (I ended up turning that fic into a monster -- 106K, and I took 9 months to finish it -- and by the end, none of my original 'crew' of readers were still around. Sadness! In hindsight, I probably should have wrapped it up when it was still hot. But I had a lot of story I wanted to get out!)

Hmm, maybe there is a trend here! All three of these were "of the moment," at least somewhat, and all three of them were written in the middle of the night.

The formula for success?!??!?!

My SQL:

SELECT
   "Kudos"."Kudos_work",
   "Work"."Work_name",
   SUM("Kudos"."Kudos_count") AS "Kudos_count"
FROM "Fact_Kudos" AS "Kudos"
   INNER JOIN "Dim_Work" AS "Work" ON "Kudos_work" = "Work_key"
WHERE "Kudos"."Kudos_date"
   < ("Work"."Work_reveal_date" + INTERVAL '3 days')
GROUP BY
   "Kudos"."Kudos_work",
   "Work"."Work_name"
ORDER BY SUM("Kudos"."Kudos_count") desc

Current Mood: perplexed

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!

Kudos Total	Date
0	2023-02-25
1,000	2023-08-17
2,000	2023-12-12
3,000	2024-06-20
4,000	2024-10-16
5,000	2024-12-21
6,000	2025-03-12

Current Mood: peaceful

|

Entries tagged with sql

Expand Cut Tags

Giftees who never kudosed

Average days between posting

SQL to find fics a certain user has not yet kudosed

Kudos velocity

SQL for the Date I Hit a Certain # of Kudos

Profile

April 2026

Most Popular Tags

Page Summary

Syndicate

Style Credit