Query Results CTE Recursive?

CTE is supported in SQLite via a WITH clause in v3.8.3 and higher:

Is this supported in Redash? The autocomplete doesn’t list the WITH clause as an option.

Answering my own question, the answer is yes! Redash does support WITH clause with recursive CTE queries if needed. Really cool!

For anyone curious, here’s a simple recursive CTE that fills a list of week-day pairs:

WITH

  weeks as (SELECT 1 "num" UNION ALL SELECT "num" + 1 FROM weeks WHERE "num" + 1 < 53)
, days(dayname) as (SELECT * FROM ( VALUES ('Sunday'), ('Monday'), ('Tuesday'), ('Wednesday'), ('Thursday'), ('Friday'), ('Saturday')))

SELECT * From weeks JOIN days ON 1 = 1

Paste this into QRDS and execute it to see the results.

Tricks like these are useful when you need to “fill-in” missing time-series data via QRDS.