Bluesky Feed Queries

catchup.sql

select
  "post"."uri",
  "post"."indexedAt",
  (
    "post"."interactionCount" - "author"."medianInteractionCount"
  ) * 1000 / ("author"."medianInteractionCount" + 1) as "rating"
from
  "post"
  inner join "author" on "post"."author" = "author"."did"
  inner join "following" on "post.author" = "following"."following"
where
  "following"."followedBy" = ?
  and "post"."indexedAt" >= ?
order by
  "rating" desc,
  "indexedAt" desc
limit
  ?
offset
  ?

goodstuff.sql

with
  "authorScoredPost" as (
    select
      "post"."uri",
      "post"."indexedAt",
      "post"."author",
      "post"."replyParentAuthor",
      "post"."interactionCount",
      (unixepoch ('now') - unixepoch ("indexedAt")) / (24.0 * 3600) as "timeAgo",
      (
        unixepoch ("indexedAt") - unixepoch ('now', '-7 days')
      ) / (24.0 * 3600) as "tScore",
      min(
        3000,
        (
          "post"."interactionCount" - "author"."medianInteractionCount"
        ) * 1000 / (author."medianInteractionCount" + 1)
      ) as "piScore"
    from
      "post"
      inner join "author" on "post"."author" = "author"."did"
  ),
  "interactionScoredPost" as (
    select
      "authorScoredPost"."uri",
      "authorScoredPost"."indexedAt",
      "authorScoredPost"."author",
      "authorScoredPost"."replyParentAuthor",
      "following"."userInteractionRatio",
      "following"."followedBy",
      "authorScoredPost"."piScore",
      0.00005 * "authorScoredPost"."timeAgo" * "authorScoredPost"."piScore" as "pScore",
      0.20 * "following"."userInteractionRatio" * "authorScoredPost"."timeAgo" as "iScore",
      (
        unixepoch ("indexedAt") - unixepoch ('now', '-7 days')
      ) / (24.0 * 3600) as "tScore"
    from
      "authorScoredPost"
      inner join "following" on "authorScoredPost"."author" = "following"."following"
  )
select
  "interactionScoredPost"."uri",
  "interactionScoredPost"."indexedAt",
  "interactionScoredPost"."author",
  "interactionScoredPost"."userInteractionRatio",
  "interactionScoredPost"."piScore",
  "interactionScoredPost"."iScore",
  "interactionScoredPost"."tScore",
  "pScore" + "tScore" + "iScore" as "rating"
from
  "interactionScoredPost"
  left join "following" as "parentFollowing" on "interactionScoredPost"."replyParentAuthor" = "parentFollowing"."following"
where
  "interactionScoredPost"."followedBy" = ?
  and (
    "replyParentAuthor" is null
    or "parentFollowing"."followedBy" = ?
  )
union all
select
  "authorScoredPost"."uri",
  "authorScoredPost"."indexedAt",
  "authorScoredPost"."author",
  0 as "userInteractionRatio",
  0 as "piScore",
  0 as "iScore",
  "authorScoredPost"."tScore",
  "authorScoredPost"."tScore" as "rating"
from
  "authorScoredPost"
where
  "authorScoredPost"."author" = ?
order by
  "rating" desc
limit
  ?
offset
  ?

lovelies.sql

with
  scoredPost as (
    select
      "post"."uri",
      "post"."indexedAt",
      "post"."author",
      "following"."userInteractionRatio",
      "following"."followedBy",
      "post"."replyParent",
      (
        "userInteractionRatio" * (unixepoch ('now') - unixepoch ("indexedAt")) / (24.0 * 3600) * 0.20
      ) as "iScore",
      (
        unixepoch ("indexedAt") - unixepoch ('now', '-7 days')
      ) / (24.0 * 3600) as "tScore"
    from
      "post"
      inner join "following" on "post"."author" = "following"."following"
  )
select
  "uri",
  "indexedAt",
  "author",
  "userInteractionRatio",
  "iScore",
  "tScore",
  "iScore" + "tScore" as "rating"
from
  "scoredPost"
where
  "followedBy" = ?
  and "replyParent" is null
order by "rating" desc
limit
  ?
offset
  ?

the-quiet-people.sql

with
	"maxPostCount" as (
		select max("postCount") as cnt, ifnull(log(max("postCount")),1) as logCnt
		from "following"
		inner join "author" on "following"."following" = "author"."did"
		where "following"."followedBy" = ?
	),
	"authorBoost" as (
		select "author"."did", 7 - 14 * ifnull(log("postCount"),0) / (select "logCnt" from "maxPostCount") as "boostScore"
		from "following"
		inner join "author" on "following"."following" = "author"."did"
		where "following"."followedBy" = ?
	)
select
  "post"."uri",
  "indexedAt",
  "author",
  "boostScore",
  (
    unixepoch ("indexedAt") - unixepoch ('now', '-7 days')
  ) / (24.0 * 3600) as "tScore"
from "post"
inner join "authorBoost" on "post"."author" = "authorBoost"."did"
left join "following" as "parentFollowing" on "post"."replyParentAuthor" = "parentFollowing"."following"
where (
  "replyParentAuthor" is null
  or "parentFollowing"."followedBy" = ?
)
and post."indexedAt" >= ?
order by "tScore" + "boostScore" / 7 desc
limit
  ?
offset
  ?

you-might-like.sql

select
  post.uri,
  (
    unixepoch ("indexed_at") - unixepoch ('now', '-7 days')
  ) / (24.0 * 3600) as t_score
from
  post_interacted_by_followed as post
  left join post_interacted_by_followed_author as author on post.author = author.author
  and post.user = author.user
  left join following on following.following = post.author
  and following.followedBy = ?
where
  post.user = ?
  and post.author <> ?
  and following.followedBy is null
order by
  10 * post.followed_interaction_count + author.followed_interaction_count + 100 * t_score DESC
limit
  ?
offset
  ?