SQL Proof of Concept for Collating Different Article Feed Selection Criteria for DEV.to

Scratching Down Some Ideas to Share

I previously wrote about the Diving into Dev’s Relevancy Feed Builder. And what follows is a quick proof of concept regarding a possible feature for the DEV.to feed.

What we want is to run an experiment in which we inter-weave two different sets of articles to create the relevancy feed. Some constraints are:

1. There are two sets of articles.
2. We don’t want duplicate articles.
3. We want a primary set and a secondary set.
4. We want the secondary set to be interspersed within the primary set.
5. How we select what is in each set is it’s own unique problem.

Proof of Concept SQL and Result Set

What follows is a quick Postgresql query that is database agnostic. Basically, can I setup a collation of two sets of data. There are some refinements to how I collate, but I’m presently satisfied with the bones of the query.

WITH integers AS (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY '1') AS collation_order,
    'integers' AS table_source,
    ,*
    FROM generate_series(1,12)
), threes AS (
  SELECT
    (ROW_NUMBER() OVER (PARTITION BY '1') * 0.75 + 0.1) AS collation_order,
    'threes' AS table_source,
    ,*
    FROM generate_series(3,27,3)
)
    (
      (SELECT * FROM integers)
      UNION
      (SELECT * FROM threes
    WHERE generate_series NOT IN (
      SELECT generate_series FROM integers))
    ) ORDER BY collation_order

The results are as follows:

Conclusion

I’m not yet certain if this is something I’ll incorporate into the possible Feed Variants, but I wanted to put this out there.