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.
This site is in read only mode. Please continue to browse, but replying, likes,
and other actions are disabled for now.
emaynard
2
Answering my own question, the answer is yes! Redash does support WITH clause with recursive CTE queries if needed. Really cool!
jesse
3
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.