Yes, this maps quite nicely to SQL. Consider this pseudocode[1]:
for customer in (select distinct c.customer_id, c.name from customers c natural join purchases p natural join addresses a where p.purchased_at >= last_year and a.city = 'Oregon') {
println(customer.name)
for purchase in (select date, amount from purchases where customer_id = customer.customer_id and purchased_at >= last_year) {
println("\t"+purchase.date+": "+purchase.amount)
}
println()
}
I know, you're already recoiling in horror. But that's because you've grown accustomed to having to deal with shitty implementations of SQL. It is recognized that you cannot actually write code like that under those shitty implementations because your program will quickly grind to a halt as soon as you gain a few customers, but the point here is to stop using shitty SQL implementations and embrace SQL as it as it was actually intended to be used.> But in my opinion, this does not mean that SQL should not have provided a native solution for this.
SQL does provide a native solution. But it seems your argument is, perhaps, that it should formally define a way to hack around shitty implementations? Why not use the right tool for the job instead?
> I also think that SQL teaching generally emphasizes joins as the natural way to work with relational databases way to much.
Agreed, and I am suggesting that is because of the hacks required to work around the reasons why you cannot write the above code under shitty implementations, which has lead to many to conflate joins and view aggregation.
If you want something like the above except using a declarative language instead then you can put something like GraphQL in front of it. What are you really gaining by turning SQL into GraphQL? SQL is not intended for graph databases. It is a different tool for a different job.
[1] The need for distinct here is SQL's actual flaw. If it were truly relational like it likes to pretend to claim to be that would be unnecessary. It brought the relational calculus but not the relational algebra, which leaves a funny impedance mismatch. You can deal with that in the small scale well enough, but as queries become complex they become hard to reason about because they don't behave like the expression suggests. Which serves as a good corollary as to why turning SQL into GraphQL is unwise when it doesn't have a graph foundation. Primitives best match the primitives. You can layer the abstractions on top, where you can take responsibility for the leaks, as your specific needs dictate.