SQL Essentials

Harbor Coffee's real data doesn't live in a file. It lives in a database, with billions of rows, and you can't load that into pandas. To reach it, you need SQL. It's fifty years old, it's used in nearly every company, and it's the first thing data interviews test.

• Data lives in databases
• Too large for pandas
• SQL is fifty years old and everywhere

The core of SQL

Duck DB lets us run SQL right on our files, with no server. Read this query in plain English. Select city and total revenue, from the sales, where the category is coffee, grouped by city, ordered from highest, and take the top four. Run it. It's the same as a pandas group by. Nearly all SQL follows this same pattern, and once you've learned it, you can talk to any database in the world.

import duckdb

print(duckdb.sql("""
    SELECT city, SUM(revenue) AS revenue
    FROM 'sales.csv'
    WHERE category = 'Coffee'
    GROUP BY city
    ORDER BY revenue DESC
    LIMIT 4
""").df().round(0).to_string(index=False))

  city  revenue
 Osaka 515985.0
 Milan 442265.0
Bergen 425166.0
 Tokyo 408964.0

Joining in SQL

Here's our profit per square metre, in SQL. Join connects sales to stores, using store ID. And note the difference between where and having. Where filters rows before grouping. Having filters groups after. Run it. Giza earns the least for its floor space. Interviewers love asking about where versus having.

import duckdb

print(duckdb.sql("""
    SELECT s.city, st.size_m2,
           ROUND(SUM(s.profit) / st.size_m2) AS profit_per_m2
    FROM 'sales.csv' AS s
    JOIN 'stores.csv' AS st USING (store_id)
    GROUP BY s.city, st.size_m2
    HAVING SUM(s.profit) > 0
    ORDER BY profit_per_m2
    LIMIT 3
""").df().to_string(index=False))

  city  size_m2  profit_per_m2
  Giza      171         2009.0
 Milan      197         2226.0
Bergen      183         2300.0

Summary