I'm reviewing some of our Redshift queries and found cases with multiple levels of nested select like the one below:
LEFT JOIN
(
SELECT *
FROM (
SELECT
id,
created_at,
min(created_at) OVER (PARTITION BY id, slug) AS transition_date
FROM table
WHERE status = 'cancelled'
GROUP BY id, Y, Z, created_at
)
WHERE created_at = transition_date
) t1 ON b.id = t1.id
if this were MySQL, I would've done something like this to remove one level of nested select:
LEFT JOIN
(
SELECT
id,
created_at,
@tdate := min(created_at) OVER (PARTITION BY id, slug) AS transition_date
FROM table
WHERE status = 'cancelled' and @tdate = bul.created_at
GROUP BY id, Y, Z, created_at
) t1 ON b.id = t1.id
Is it possible to so something similar in RedShift?
--- update forgot to include GROUP BY in the nested SELECT, which may affect the answer
You can move the condition for the transition_date into the JOIN condition:
LEFT JOIN
(
SELECT
id,
created_at,
min(created_at) OVER (PARTITION BY id, slug) AS transition_date
FROM table
WHERE status = 'cancelled'
) t1 ON b.id = t1.id AND t1.created_at = t1.transition_date