Practical SQL for Data Analysis

I guess if you have huge amounts of data (10m+ rows) already loaded into a database then sure, do your basic summary stats in SQL.

For everything else, I'll continue using SQL to get the data from the database and use Pandas or Data.Table to actually analyze it.

That said, this is a very comprehensive review of SQL techniques which I think could be very useful for when you do have bigger datasets and/or just need to get data out of a database efficiently. Great writeup and IMO required reading for anyone looking to be a serious "independent" data scientist (i.e. not relying on data engineers to do basic ETL for you).

I'd be a huge fan of something like the PySpark DataFrame API that "compiles" to SQL* (but that doesn't require you to actually be using PySpark which is its own can of worms). I think this would be a lot nicer for data analysis than any traditional ORM style of API, at least for data analysis, while providing better composability and IDE support than writing raw SQL.

*I also want this for Numexpr: https://numexpr.readthedocs.io/en/latest/user_guide.html, but also want a lot of other things, like a Arrow-backed data frames and a Numexpr-like C library to interact with them.

  WITH dt AS (
      SELECT unnest(array[1, 2]) AS n
  )
  SELECT * FROM dt;

  SELECT n FROM unnest(array[1, 2]) n;
  ┌───┐
  │ n │
  ├───┤
  │ 1 │
  │ 2 │
  └───┘
  (2 rows)

I know the code is from a section dealing with CTEs, but CTEs aren't needed for every situation including things like using VALUES lists. Most people in the target audience can probably ignore this next point (probably on a newer version of PostgreSQL), but older versions of PostgreSQL would materialize the CTE results prior to processing the main query, which is not immediately obvious.