The thought process can be the same in SQL. You can start by writing SELECT * FROM Account; then add your JOIN to User, then add your predicates. Then you can refine it – here, if I’m understanding the code correctly, you’re using User for a JOIN but never return anything from that table, so you could turn it into a semi-join (WHERE EXISTS) and likely get a speed-up.
The fundamentals of software design. DRY
If you have a complex predicate that defines a set of rows, and you use that set in many different queries, are you rewriting it each time?
So you don't actually have an answer for me?
Maybe I do, maybe I don't. It depends. Most likely I won't, unless it's used in like 80 % or more of queries and the problem domain guarantees that it will basically never change and if it does it will for sure, absolutely, change everywhere at once.
I like easily testable SQL queries that are easy to read, right there in the code. No need to execute anything to get to the SQL that would execute in the database. 'But then I need to write thousands of queries?!' Yes, you do, and you'll have a chill and easy time whenever they change, and they will if your software gets use and your data grows. Because you can just search/replace on the SQL text, and don't need to figure out every place some mutilated piece might get called from and probably get it wrong a few times or need to figure out how to split it into two or more fragments.
In some cases you can figure out a robust way to generate SQL but it's rare, it mostly comes up in one-off scenarios.
> then add your predicates
How are you canonically storing these predicates in code to reuse over 5-10 queries?