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.