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 backdates 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.

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