With rare exception, I write all my own SQL queries. I’m a big fan of CTEs over subqueries because I think they’re easier to interpret for others who swim down of a query and make for cleaner code, especially if I’m marrying multiple datasets.
I’m also a fan of the CASE function because of the ability it gives analysts and data scientists to segment data on the fly. I recommend using a data pipelining tool, like dbt, so these data definitions are one and done. I’ve repeatedly run into the issue where analysts take different approach to data cleaning, e.g., how to handle nulls, weighted values, query parameters for urls, etc. With a pipelining tool, you can define these rules and then apply them to new pipelines. 😙👌