writing large SQL queries

[edublancas]

The best way to write large SQL queries is via CTEs:

WITH something AS (
  SELECT * FROM some_table
  WHERE column = 1
)
SELECT *
FROM something
WHERE another_column = 2

However, when writing CTEs, we lose the ability to run the sub-expressions interactively.

We could add something like this:

%%sql --name something
SELECT * FROM some_table
WHERE column = 1

Then, to re-use the previous query:

%%sql
SELECT *
-- refer to the previous query
FROM ':something'
WHERE another_column = 2

And have the extension automatically expand the expression above to:

WITH something AS (
  SELECT * FROM some_table
  WHERE column = 1
)
SELECT *
FROM something
WHERE another_column = 2

Before submitting to the database.

[vikjam]

Copying Slack thread over to this GitHub issue.

Instead of converting a subquery to a CTE for the user, it might be more explicit to have them specify the CTEs in the magics function. For example,

%%sql --with something
SELECT *
FROM something
WHERE another_column = 2