"No composability or code reuse" is definitely a valid criticism of SQL. Check out the very first example of my personal project https://docs.racket-lang.org/plisqin/Read_Me_First.html and let me know how you would implement something akin to `(CategoryName p)` and `(TotalSales p)` in SQL. A view does not count, because then you either have one view per derived field and that is very cumbersome to consume, or you have one view with all the derived fields and you will end up paying performance costs for fields that you are not using.

Don't views and materialized views give you reuse?

Can't you do composibility with foreign references?

Sure, while views may be fine for small projects, using them for larger projects like data warehousing is usually a mistake you'll come to regret.

SQL views are rarely unit tested so you always end up in a regression nightmare when you need to make updates.

If you're going to go pure SQL, you should use something like dbt.

A more useful comment would be to illustrate how. I'd like to know as well as I need to reuse queries in SQL occasionally but am not an expert. Currently I believe learning pl/pgsql is the answer, but even it reeks of punch-cards and other sixtiesisms :-). Tough sell when you're used to Python etc.

That could be said about any tool, framework, language, library, etc. Invest enough time and you can do everything with malbolge but that doesn't mean it doesn't suck.

I am a pandas user considering refactoring part of my ETL pipeline to SQL. I see the trade off as memory efficiency vs expressiveness, and for simple queries on big data, SQL wins. Would you disagree that Pandas/Python is more expressive than SQL? I’m less experienced in SQL but based on my limited experience there, it seems Pandas is clearly more expressive. What is the SQL equivalent of Pandas .apply(lambda x) ?

ClickHouse has lambdas for arrays. They are very useful. Here's an example.

  WITH ['a', 'bc', 'def', 'g'] AS array
  SELECT arrayFilter(v -> (length(v) > 1), array) AS filtered
  
  ┌─filtered─────┐
  │ ['bc','def'] │
  └──────────────┘

The lambda in this case is a selector for strings with more than one character. I would not argue that they are as general as Pandas, but they are might useful. More examples from the following article.

https://altinity.com/blog/harnessing-the-power-of-clickhouse...

Please define an example lambda function, in order to see whether there is an sql equivalent. Imo >90% of the data issues i have seen, can be solved with sql queries. I have seen some in the finance sector which would require super complex udfs, but other than these, sql is the first choise to solve a data issue.

I do work in finance sector and need complex functions :)

And it’s interesting that pandas was invented at a hedge fund, AQR.

I agree that SQL may be better for vanilla BI.