Average days between posting

[artemisdart]

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.