I have a hypothetical query as such: I am deriving something that is essentially a "registration date" for users. I then want to reuse this subquery in numerous places throughout another query to gather values such as "total orders 1 year before registration" and "total orders 1 year after registration".
My question is, is there a way to write this more cleanly (and more readable) and make sure this query isn't running multiple times?
For what it's worth, the subquery to derive the "registration date" is more complex than shown here.
`select * from
users
left join (select sum(orders) WHERE (some condition) group by user_id having
date < (SELECT
ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date ASC) AS rn
FROM table1 ph
JOIN table2
) ) as orders_1yr_after_registration on orders_1yr_after_registration.user_id = users.userid
left join (select sum(orders) WHERE (some condition) group by user_id having
date < (SELECT
ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date ASC) AS rn
FROM table1 ph
JOIN table2
) ) as orders_1yr_before_registration on orders_1yr_before_registration.user_id = users.userid
left join (select sum(orders) WHERE (some condition) group by user_id having
date > (SELECT
ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date ASC) AS rn
FROM table1 ph
JOIN table2
) ) as orders_3yr_before_registration on orders_1yr_before_registration.user_id = users.userid