Thank you for this post Helen!
I think CTEs are not that used, at least by web devs, because they don't fit well the ORM fairy tale in which data is mapped to objects.
I understood their power better by working side to side with data scientists using Redshift, kudos a #dataenthusiast like yourself!
Subqueries are still valuable if the relationship between the two queries is simple and the subquery is short but CTE are way better! Giving a name to a query can make all the difference.
The same goes for views I guess. I suspect that Heroku's Dataclips are built with something like that behind the scenes!
Thanks for your response, glad you found it useful.
Great to hear you are working with a data scientist too. :)
Another reason why CTEs are not that popular is that MySQL did not support recursive CTEs till recently (version 8).
Using CTEs on Postgres made a lot of complex queries easy to maintain and explain to team members.
We're a place where coders share, stay up-to-date and grow their careers.
We strive for transparency and don't collect excess data.