Recently I’ve been wondering how long it would take me to get to 1,000,000 reviews. Right now I’m sitting at between 800,000 and 900,000 reviews and for no other reason than pure ridiculous curiosity I was curious whether I could get SQLite to compute it directly for me. Turns out the answer is “yes, you can.”

Here’s the query in its gory detail and then I’ll walk through how it works:

WITH 
daily_review_rate AS (
  SELECT AVG(reviews_per_day) as avg_daily_reviews
  FROM (
    SELECT 
      date(ROUND(id/1000), 'unixepoch') as review_date,
      COUNT(*) as reviews_per_day
    FROM revlog
    WHERE date(ROUND(id/1000), 'unixepoch') >= date('now', '-2 years')
    GROUP BY review_date
    HAVING reviews_per_day >= 25
  )
),
current_total AS (
  SELECT COUNT(*) as total_reviews
  FROM revlog
)
SELECT 
  ROUND((1000000 - total_reviews) / avg_daily_reviews) as days_to_million,
  date('now', '+' || ROUND((1000000 - total_reviews) / avg_daily_reviews) || ' days') as target_date
FROM current_total, daily_review_rate
WHERE total_reviews < 1000000;

Today when I run the query, I get: days_to_million = 698 and target_date - 2026-12-12.

Common Table Expressions

The first step in understanding the query is to grasp Common Table Expressions (CTEs) which are like functions that compute intermediate values for use in the main query. You can recognize CTEs by the use of WITH:

WITH 
  cte1 AS (...),
  cte2 AS (...),
  cte3 AS (...)
SELECT ... FROM ...

It’s basically telling SQLite, before you run the main query, compute these intermediate steps first.

Each CTE defined in the WITH clause:

• Can be referenced by name in the main query
• Can reference CTEs defined before it (like cte2 could use cte1)
• Only exists for the duration of the query
• Can be used multiple times in the main query if needed

We could get by without CTEs here if we were willing to compromise legibility by using more nested queries.

I’ll break down the query step by step:

1. First, we have two Common Table Expressions (CTEs) that prepare our data:

   WITH 
   daily_review_rate AS (...)
   current_total AS (...)
   

2. Let’s look at the inner workings of daily_review_rate:

   SELECT AVG(reviews_per_day) as avg_daily_reviews
   FROM (
     SELECT 
       date(ROUND(id/1000), 'unixepoch') as review_date,
       COUNT(*) as reviews_per_day
     FROM revlog
     WHERE date(ROUND(id/1000), 'unixepoch') >= date('now', '-2 years')
     GROUP BY review_date
     HAVING reviews_per_day >= 25
   )
   

   • The innermost subquery converts millisecond timestamps to dates (date(ROUND(id/1000), 'unixepoch'))
   • Groups these by date and counts reviews per day
   • Filters for days with 25+ reviews
   • The innter query only accepts reviews in the last 2 years so that we only consider these more recent data in the calculations.
   • The outer query then averages these daily counts

3. The current_total CTE is simpler:

   SELECT COUNT(*) as total_reviews
   FROM revlog
   

   • Just counts all reviews ever done

4. The main query brings it all together:

   SELECT 
     ROUND((1000000 - total_reviews) / avg_daily_reviews) as days_to_million,
     date('now', '+' || ROUND((1000000 - total_reviews) / avg_daily_reviews) || ' days') as target_date
   FROM current_total, daily_review_rate
   WHERE total_reviews < 1000000;
   

   • Calculates remaining reviews needed (1000000 - total_reviews)
   • Divides by average daily rate to get days needed
   • Uses SQLite’s date function to add these days to today
   • Only returns results if we haven’t hit 1 million yet

Each step builds on the previous ones, using the CTEs like building blocks to construct the final calculation. The query is structured this way to make each calculation clear and separate, rather than trying to do everything in one giant nested query.

Enjoy! You can run the query from the sqlite3 on the command line if you want, or if you’re on macOS you could use Base or another GUI SQLite application. Happy reviewing!

For any questions, you can get in touch at my contact page.

This isn’t the first time, but I hope it will be the last.

Facebook, for me has long been a source of enjoyment and connection. But it also leaves me feeling cringey. So what changed?

What changed is that Facebook has gone full-on MAGA and I’m not OK with that:

• “Meta CEO Mark Zuckerberg met with President-elect Donald Trump on Friday [January 10, 2025] at Mar-a-Lago, two sources familiar tell CNN. Meta declined to comment on the meeting between Zuckerberg and Trump.” - Source
• Meta said today [January 7, 2025] it will end its fact-checking program in exchange for X-style community notes as part of a slate of changes targeting ‘censorship’ and embracing ‘free expression’. - Source,
  • We all know how this has gone at “X”, where self-proclaimed “free speech absolutist” has actively shaped pro-Republican messaging on the platform.
• “Joel Kaplan, a prominent Republican, replaced Meta’s policy chief Nick Clegg last week. (He said Meta’s third-party fact-checkers have demonstrated ‘too much political bias’ in a Fox News interview this morning [January 7, 2025.)” - Source
• “CEO Mark Zuckerberg dined at Mar-a-Lago on Thanksgiving eve. [November 27, 2024]” - Source
• “The company [Meta/Facebook] pledged a $1 million donation to Trump’s inauguration.” - Source
• “On Monday, it [Meta] added three people to its board, including close Trump ally Dana White.” - Source
  • I didn’t know who Dana White was but he appears to be the president and CEO of Ultimate Fighting Championship (UFC) and the owner of Power Slap, which is a “slap fighting” promotion, whatever that is. The bottom line is that he sounds like he’s rich and into violence, just the type of person that would appeal to Tr*mp.

So thanks for the memories, Facebook. But for me this is the end of the road.

I write things here from time-to-time and you can also get in touch at my contact page.