You can, but then every step starts with a drop table if exists; insert into …

cess11 · 2025-05-22T13:57:10 1747922230

Or you nest your queries:

    select second from (select 42 as first, (select 69) as second);

Intermediate steps won't be stored but until queries take a while to execute it's a nice way to do step-wise extension of an analysis.

Edit: It's a rather neat and underestimated property of query results that you can query them in the next scope.

wodenokoto · 2025-05-23T07:08:37 1747984117

We all have different definitions on what is difficult. Maybe annoying or bothersome had been better words, but below beats nesting things:

    df |> select(..) |>
        filter(...) |>
        mutate(...) |>
        ...

And every time I've learned something about the intermediate result I can add another line, or save the result in a new variable and branch my exploration. And I can easily just highlight and run and number of of steps from step 1 onwards.

Even oldschool

    df2 <- df[...]
    df2 <- df2[...]

Gives me the same benefit.

cess11 · 2025-05-23T08:44:54 1747989894

Yeah, sure, I do a lot of such things in RAM in Elixir, some Lisp, PHP or, if I must, Python.

But sometimes I just happen to have just imported a data set in a SQL client or I'm hooked into a remote database where I don't have anything but the SQL client. When developing an involved analysis query nesting also comes in handy sometimes, e.g. to mock away a part of the full query.

jcheng · 2025-05-22T16:21:54 1747930914

Or better yet, use CTEs: https://duckdb.org/docs/stable/sql/query_syntax/with.html

cess11 · 2025-05-23T06:09:36 1747980576

Absolutely, if the engine has them and they're not wonky somehow.