In this post we are going to cover how to write, debug and speed up SQL with Claude on a real PostgreSQL 16 database. We start with a reporting query written in the Claude app. Then Claude Code runs it, fixes a broken report, makes a slow query fast with an index, and the last step is a monthly report built with window functions.
Two tools are in play here. The Claude app is the chat. It can write a query, but it cannot see your database: it only knows what you give it. Claude Code works inside your project folder. It can read your files and run commands in your terminal, so it can run psql for you, read the real error, and check the real result. For a data engineer that is the important part. Every result in this post comes from really running the query, not from a guess.
Part 3 of the Claude for Data Engineers series on TechBrothersIT.
Let's create some sample data
Our project is shop-sql, a small invented online shop with three tables: 5,000 customers, 100,000 orders from January to September 2026, and one payment for every order that was not cancelled. The seed script uses hashint4(), so the data looks random but comes out the same every time. There is also a CLAUDE.md file that says how to connect and how to run SQL files, and three report queries in sql/. Here are the starting files and the command that loads the schema and the data.
# shop-sql
Online shop reporting database (PostgreSQL 16).
- Connection: PG* env vars, so plain `psql` works.
- Run a SQL file with
`psql -v ON_ERROR_STOP=1 -f <file>`
(stops at the first error, exits non-zero).
- Schema: `db/schema.sql`
- Schema changes: `db/migrations/NNN_name.sql`
- Reports: `sql/`, one query per file,
with a comment on top.-- Online shop schema (PostgreSQL 16)
CREATE TABLE customers (
customer_id integer PRIMARY KEY,
full_name text NOT NULL,
country text NOT NULL,
signup_date date NOT NULL
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
customer_id integer NOT NULL REFERENCES customers (customer_id),
order_date date NOT NULL,
status text NOT NULL, -- placed, shipped, cancelled
total numeric(10,2) NOT NULL
);
CREATE TABLE payments (
payment_id integer PRIMARY KEY,
order_id integer NOT NULL REFERENCES orders (order_id),
paid_at timestamp NOT NULL,
method text NOT NULL, -- card, paypal, gift_card
amount numeric(10,2) NOT NULL
);-- Invented demo data: 5,000 customers, 100,000 orders (Jan to Sep 2026),
-- one payment for every order that was not cancelled. hashint4() makes it look random but repeatable.
INSERT INTO customers
SELECT g,
'Customer ' || g,
CASE WHEN abs(hashint4(g)) % 100 < 35 THEN 'United States'
WHEN abs(hashint4(g)) % 100 < 55 THEN 'United Kingdom'
WHEN abs(hashint4(g)) % 100 < 73 THEN 'Germany'
WHEN abs(hashint4(g)) % 100 < 88 THEN 'Canada'
ELSE 'India' END,
date '2025-01-01' + abs(hashint4(g + 1)) % 365
FROM generate_series(1, 5000) AS g;
INSERT INTO orders
SELECT g,
1 + abs(hashint4(g + 2)) % 5000,
date '2026-01-01' + floor(273 * power((abs(hashint4(g + 3)) % 100000) / 100000.0, 0.8))::int,
CASE WHEN g % 20 = 0 THEN 'cancelled' WHEN g % 20 = 1 THEN 'placed' ELSE 'shipped' END,
12 + abs(hashint4(g + 4)) % 480 + (abs(hashint4(g + 5)) % 100) / 100.0
FROM generate_series(1, 100000) AS g;
INSERT INTO payments
SELECT o.order_id,
o.order_id,
o.order_date + time '08:00' + (o.order_id % 600) * interval '1 minute',
(ARRAY['card', 'paypal', 'gift_card'])[1 + o.order_id % 3],
o.total
FROM orders o
WHERE o.status <> 'cancelled';
ANALYZE;-- Revenue by country for September 2026 (paid orders only)
SELECT c.country,
count(DISTINCT o.order_id) AS paid_orders,
sum(p.amount) AS revenue,
round(avg(p.amount), 2) AS avg_order_value
FROM payments p
JOIN orders o ON o.order_id = p.order_id
JOIN customers c ON c.customer_id = o.customer_id
WHERE p.paid_at >= date '2026-09-01'
AND p.paid_at < date '2026-10-01'
GROUP BY c.country
ORDER BY revenue DESC;-- Top 10 customers by paid revenue
SELECT c.customer_id,
c.country,
count(o.order_id) AS orders,
sum(p.amount) AS revenue,
o.order_date AS last_order
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN payments p ON p.order_id = o.order_id
GROUP BY c.customer_id, c.country
ORDER BY revenue DESC
LIMIT 10;-- Order history page: the 10 latest orders of one customer (runs on every page view)
SELECT order_id, order_date, status, total
FROM orders
WHERE customer_id = 4242
ORDER BY order_date DESC
LIMIT 10;psql -q -v ON_ERROR_STOP=1 -f db/schema.sql -f db/seed.sqlStep 1: Write SQL in the Claude app
Let's start in the Claude app with a simple reporting question. The app cannot see our database, so first we give it the schema. We click the + button, choose Add files or photos, and attach db/schema.sql. In the same message we also write the engine and the rough table sizes: PostgreSQL 16, 5,000 customers, 100,000 orders and 95,000 payments. Then we ask the question.
Revenue by country for September 2026: paid orders, revenue, average order value.Claude answers with a PostgreSQL query. It joins payments to orders and customers, filters on the payment date, and groups by country.
Here is a PostgreSQL query for September 2026:
-- Revenue by country for September 2026 (paid orders only)
SELECT c.country,
count(DISTINCT o.order_id) AS paid_orders,
sum(p.amount) AS revenue,
round(avg(p.amount), 2) AS avg_order_value
FROM payments p
JOIN orders o ON o.order_id = p.order_id
JOIN customers c ON c.customer_id = o.customer_id
WHERE p.paid_at >= date '2026-09-01'
AND p.paid_at < date '2026-10-01'
GROUP BY c.country
ORDER BY revenue DESC;It counts only orders that have a payment, and filters on paid_at with a half-open range, so the last day of the month is included.
The filter is a half-open range on paid_at: >= '2026-09-01' and < '2026-10-01'. So every payment on September 30 is included, whatever the time of day. The query looks right, but nobody has run it yet. So we save it in the project as sql/country_revenue.sql (it is already in the starting files above) and let Claude Code run it for real.
Always give the chat three things: the table definitions, the database engine and version, and roughly how many rows each table has. Without them Claude has to guess the dialect and the data volume.
Step 2: Run it with Claude Code
Now let's run that query against the real database. Claude Code starts in auto mode. We press Shift+Tab once to switch to manual mode, so Claude asks before every command. That is a good habit whenever commands touch a database.
The project's CLAUDE.md tells Claude how to connect (the PG* environment variables) and to run SQL files with -v ON_ERROR_STOP=1, so a failing query really fails and psql exits non-zero. Let's ask Claude to run the file.
Run sql/country_revenue.sql against the shop database and show me the resultClaude says it will run it with psql, as CLAUDE.md describes. Running a command needs permission, so a prompt appears. We choose the second option, Yes, and don't ask again for psql commands. Claude Code saves that as an allow rule in .claude/settings.local.json, so the next psql command runs without asking. Here is the command and its real output.
psql -v ON_ERROR_STOP=1 -f sql/country_revenue.sql country | paid_orders | revenue | avg_order_value
----------------+-------------+------------+-----------------
United States | 4682 | 1183872.54 | 252.86
United Kingdom | 2509 | 625155.46 | 249.17
Germany | 2394 | 599358.21 | 250.36
Canada | 1923 | 489319.03 | 254.46
India | 1403 | 356387.91 | 254.02
(5 rows)Five countries. The United States is first with 4682 paid orders and 1183872.54 in revenue, then the United Kingdom with 625155.46. The average order value sits between 249.17 and 254.46 for every country. So the query from the chat works on real data.
Run new queries on a dev database or a copy first. And when Claude can execute SQL on a shared database, give it a read-only role.
Step 3: Fix a broken query
Next, a query that is broken. The top customers report in sql/top_customers.sql came from a colleague, and it fails. We ask Claude to run it, find out why, and fix it.
sql/top_customers.sql fails. Run it, find out why, and fix it.Claude runs the file first, to see the real error instead of guessing.
psql -v ON_ERROR_STOP=1 -f sql/top_customers.sqlpsql:sql/top_customers.sql:12: ERROR: column "o.order_date" must appear in the GROUP BY clause or be used in an aggregate function
LINE 5: o.order_date AS last_order
^Postgres stops at line 5 with column "o.order_date" must appear in the GROUP BY clause or be used in an aggregate function, and because of ON_ERROR_STOP psql exits with code 3. Claude reads the file and explains the problem. o.order_date is neither grouped nor aggregated, and a customer has many orders, so one order date per customer row makes no sense. What the report really wants is the last order date, max(o.order_date).
Claude asks before it edits the file, and we choose Yes. The fix is one line. Then it runs the report again.
-- Top 10 customers by paid revenue
SELECT c.customer_id,
c.country,
count(o.order_id) AS orders,
sum(p.amount) AS revenue,
max(o.order_date) AS last_order
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN payments p ON p.order_id = o.order_id
GROUP BY c.customer_id, c.country
ORDER BY revenue DESC
LIMIT 10;psql -v ON_ERROR_STOP=1 -f sql/top_customers.sql customer_id | country | orders | revenue | last_order
-------------+---------------+--------+----------+------------
3541 | Germany | 38 | 10989.15 | 2026-09-28
2948 | United States | 36 | 10952.06 | 2026-09-26
1694 | Germany | 36 | 9625.52 | 2026-09-26
4216 | United States | 35 | 9395.68 | 2026-09-25
4624 | Canada | 30 | 9241.30 | 2026-09-21
3728 | United States | 32 | 9079.05 | 2026-09-23
2959 | United States | 28 | 8861.57 | 2026-09-20
1561 | India | 34 | 8789.55 | 2026-09-30
109 | United States | 30 | 8731.42 | 2026-09-06
3555 | United States | 29 | 8687.41 | 2026-09-19
(10 rows)Now it works: the top 10 customers by paid revenue, each with the date of their last order. Customer 3541 from Germany is first with 38 orders and 10989.15, and customer 2948 from the United States is close behind with 10952.06. The last_order column now shows real dates like 2026-09-28 instead of an error.
Let Claude run the failing query itself, or paste the full error message. A real error is always better than a guess.
Step 4: Speed up a slow query
Now let's make a slow query fast. The order history page runs sql/customer_orders.sql on every visit: the 10 latest orders of one customer. We ask Claude why it is slow and how to fix it.
The order history query in sql/customer_orders.sql is slow. Why, and how do we fix it?Claude reads the file, then runs EXPLAIN (ANALYZE, BUFFERS, COSTS OFF). ANALYZE really runs the query and shows the plan Postgres used, with real timings. BUFFERS adds how many pages each step touched. COSTS OFF hides the planner's estimates to keep the plan short.
psql -c "EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT order_id, order_date, status, total FROM orders WHERE customer_id = 4242 ORDER BY order_date DESC LIMIT 10" QUERY PLAN
---------------------------------------------------------------------------
Limit (actual time=5.548..5.550 rows=10 loops=1)
Buffers: shared hit=739
-> Sort (actual time=5.546..5.547 rows=10 loops=1)
Sort Key: order_date DESC
Sort Method: top-N heapsort Memory: 26kB
Buffers: shared hit=739
-> Seq Scan on orders (actual time=0.442..5.514 rows=27 loops=1)
Filter: (customer_id = 4242)
Rows Removed by Filter: 99973
Buffers: shared hit=736
Planning:
Buffers: shared hit=83
Planning Time: 0.291 ms
Execution Time: 5.589 ms
(14 rows)Read a plan from the inside out. The Seq Scan on orders at the bottom feeds the Sort, and the sort feeds the Limit at the top. Here is the problem: a sequential scan. Postgres reads the whole orders table and throws away 99973 rows (Rows Removed by Filter) to find 27 orders, then sorts them to keep 10. The scan touched 736 pages (shared hit=736) to return 10 rows. Pages barely change from run to run, so they are a fairer measure than milliseconds.
Claude suggests an index on (customer_id, order_date DESC). It finds one customer's orders and returns them already sorted. Claude writes the index as a migration file, so the change is tracked, and builds it CONCURRENTLY, so the live table keeps taking new orders while the index is built. When Claude asks to create the file, we choose the second option, so the rest of this session's edits are accepted without asking. Then it runs the migration.
-- 001: speed up the order history page (filter on customer_id, newest orders first)
-- CONCURRENTLY: does not block writes on the live table (cannot run inside a transaction)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_date
ON orders (customer_id, order_date DESC);psql -v ON_ERROR_STOP=1 -f db/migrations/001_orders_customer_date_idx.sqlCREATE INDEXCREATE INDEX means the index is built. The comment in the file matters: CREATE INDEX CONCURRENTLY cannot run inside a transaction, and psql -f runs each statement on its own by default, so it works here. Now Claude runs the same EXPLAIN again.
psql -c "EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT order_id, order_date, status, total FROM orders WHERE customer_id = 4242 ORDER BY order_date DESC LIMIT 10" QUERY PLAN
------------------------------------------------------------------------------------------------------
Limit (actual time=0.029..0.065 rows=10 loops=1)
Buffers: shared hit=12
-> Index Scan using idx_orders_customer_date on orders (actual time=0.028..0.063 rows=10 loops=1)
Index Cond: (customer_id = 4242)
Buffers: shared hit=12
Planning:
Buffers: shared hit=106 read=1
Planning Time: 0.360 ms
Execution Time: 0.098 ms
(9 rows)Now Postgres uses Index Scan using idx_orders_customer_date. It reads only this customer's rows, already in the right order, so the Sort step is gone and the scan returns exactly 10 rows. That is 12 pages instead of 736, and the execution time drops from milliseconds to a fraction of a millisecond.
Indexes are not free, because every insert and update must maintain them. Add the ones your real queries need, and compare the plans before and after.
Step 5: Window functions
Last, let's use window functions for a monthly trend. We ask for paid revenue by month, with last month's revenue next to it, the growth in percent, and a running total.
New report sql/monthly_growth.sql: paid revenue by month, previous month, growth %, running totalWe are in accept edits mode now, so Claude writes sql/monthly_growth.sql without asking. A common table expression sums revenue by month. Then lag(revenue) reads the previous month's revenue, and sum(revenue) OVER w builds the running total. The WINDOW w AS (ORDER BY month) clause at the bottom names the window once, so all three functions share it. Claude runs the query right away, with no prompt, because of the psql rule we saved in step 2.
-- Monthly paid revenue: previous month,
-- growth in percent and a running total
WITH monthly (month, revenue) AS (
SELECT date_trunc('month', paid_at)::date,
sum(amount)
FROM payments
GROUP BY 1
)
SELECT month,
revenue,
-- last month's revenue
lag(revenue) OVER w AS prev_month,
-- change vs last month, in percent
round(100.0 * (revenue / lag(revenue) OVER w
- 1), 1) AS growth_pct,
-- running total
sum(revenue) OVER w AS running_total
FROM monthly
-- one named window, shared by all three
WINDOW w AS (ORDER BY month)
ORDER BY month;psql -v ON_ERROR_STOP=1 -f sql/monthly_growth.sql month | revenue | prev_month | growth_pct | running_total
------------+------------+------------+------------+---------------
2026-01-01 | 1562239.41 | | | 1562239.41
2026-02-01 | 1951585.63 | 1562239.41 | 24.9 | 3513825.04
2026-03-01 | 2394494.27 | 1951585.63 | 22.7 | 5908319.31
2026-04-01 | 2585694.48 | 2394494.27 | 8.0 | 8494013.79
2026-05-01 | 2841546.56 | 2585694.48 | 9.9 | 11335560.35
2026-06-01 | 2921685.59 | 2841546.56 | 2.8 | 14257245.94
2026-07-01 | 3152921.12 | 2921685.59 | 7.9 | 17410167.06
2026-08-01 | 3238974.16 | 3152921.12 | 2.7 | 20649141.22
2026-09-01 | 3254093.15 | 3238974.16 | 0.5 | 23903234.37
(9 rows)January has no previous month, so its prev_month and growth_pct are NULL. February is 24.9 percent up on January, and after that revenue grows every month, down to 0.5 percent in September. The running total ends September at 23903234.37, almost 24 million.
Ask Claude to put a short comment on each window function, like the ones in this file. When someone changes the report six months later, they will thank you.
The checklist: SQL with Claude
Before we finish, here is the checklist I use on real projects.
SQL with Claude, the safe way
- Give the schema, the engine and the table sizes
- Let Claude Code run the query: real errors, real results
EXPLAIN ANALYZEbefore and after every change- Dev database, read-only role, review every diff
Give Claude the schema, the engine and the table sizes. In the chat app, that is all it knows. Let Claude Code run the query, so it works from the real error and the real result. For speed, compare EXPLAIN ANALYZE before and after every change. And use a dev database and a read-only role, and review every diff before you accept it.
Key points to remember
- SQL in the Claude app from a schema
- Run it with Claude Code and psql
- Fix a GROUP BY error
- EXPLAIN ANALYZE and an index
- Window functions: lag and a running total
The full script
This bash script writes every file Claude wrote, in the same order, and runs every command, so you can reproduce all the results without Claude.
Show the full script (every file and every command, in order)
#!/usr/bin/env bash
# Claude for Data Engineers #3: Write, Debug and Speed Up SQL with Claude
# Rebuilds the demo project without Claude: writes every file Claude wrote, in the same order,
# and runs every command from the video. Run it in an empty folder.
# Tools used in the video: Python 3.13, DuckDB 2.0.0, pandas 3.0.5, PySpark 4.3.0 (local mode, Java 21),
# dbt-core 1.12.6 with dbt-duckdb 1.11.0, PostgreSQL 16. Install the ones this project uses first.
# PostgreSQL: set PGHOST, PGUSER and PGDATABASE so psql connects to an EMPTY dev database first.
mkdir -p shop-sql && cd shop-sql
# ---------- starting files ----------
cat > CLAUDE.md <<'CLAUDE_DE_EOF'
# shop-sql
Online shop reporting database (PostgreSQL 16).
- Connection: PG* env vars, so plain `psql` works.
- Run a SQL file with
`psql -v ON_ERROR_STOP=1 -f <file>`
(stops at the first error, exits non-zero).
- Schema: `db/schema.sql`
- Schema changes: `db/migrations/NNN_name.sql`
- Reports: `sql/`, one query per file,
with a comment on top.
CLAUDE_DE_EOF
mkdir -p db
cat > db/schema.sql <<'CLAUDE_DE_EOF'
-- Online shop schema (PostgreSQL 16)
CREATE TABLE customers (
customer_id integer PRIMARY KEY,
full_name text NOT NULL,
country text NOT NULL,
signup_date date NOT NULL
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
customer_id integer NOT NULL REFERENCES customers (customer_id),
order_date date NOT NULL,
status text NOT NULL, -- placed, shipped, cancelled
total numeric(10,2) NOT NULL
);
CREATE TABLE payments (
payment_id integer PRIMARY KEY,
order_id integer NOT NULL REFERENCES orders (order_id),
paid_at timestamp NOT NULL,
method text NOT NULL, -- card, paypal, gift_card
amount numeric(10,2) NOT NULL
);
CLAUDE_DE_EOF
mkdir -p db
cat > db/seed.sql <<'CLAUDE_DE_EOF'
-- Invented demo data: 5,000 customers, 100,000 orders (Jan to Sep 2026),
-- one payment for every order that was not cancelled. hashint4() makes it look random but repeatable.
INSERT INTO customers
SELECT g,
'Customer ' || g,
CASE WHEN abs(hashint4(g)) % 100 < 35 THEN 'United States'
WHEN abs(hashint4(g)) % 100 < 55 THEN 'United Kingdom'
WHEN abs(hashint4(g)) % 100 < 73 THEN 'Germany'
WHEN abs(hashint4(g)) % 100 < 88 THEN 'Canada'
ELSE 'India' END,
date '2025-01-01' + abs(hashint4(g + 1)) % 365
FROM generate_series(1, 5000) AS g;
INSERT INTO orders
SELECT g,
1 + abs(hashint4(g + 2)) % 5000,
date '2026-01-01' + floor(273 * power((abs(hashint4(g + 3)) % 100000) / 100000.0, 0.8))::int,
CASE WHEN g % 20 = 0 THEN 'cancelled' WHEN g % 20 = 1 THEN 'placed' ELSE 'shipped' END,
12 + abs(hashint4(g + 4)) % 480 + (abs(hashint4(g + 5)) % 100) / 100.0
FROM generate_series(1, 100000) AS g;
INSERT INTO payments
SELECT o.order_id,
o.order_id,
o.order_date + time '08:00' + (o.order_id % 600) * interval '1 minute',
(ARRAY['card', 'paypal', 'gift_card'])[1 + o.order_id % 3],
o.total
FROM orders o
WHERE o.status <> 'cancelled';
ANALYZE;
CLAUDE_DE_EOF
mkdir -p sql
cat > sql/country_revenue.sql <<'CLAUDE_DE_EOF'
-- Revenue by country for September 2026 (paid orders only)
SELECT c.country,
count(DISTINCT o.order_id) AS paid_orders,
sum(p.amount) AS revenue,
round(avg(p.amount), 2) AS avg_order_value
FROM payments p
JOIN orders o ON o.order_id = p.order_id
JOIN customers c ON c.customer_id = o.customer_id
WHERE p.paid_at >= date '2026-09-01'
AND p.paid_at < date '2026-10-01'
GROUP BY c.country
ORDER BY revenue DESC;
CLAUDE_DE_EOF
mkdir -p sql
cat > sql/top_customers.sql <<'CLAUDE_DE_EOF'
-- Top 10 customers by paid revenue
SELECT c.customer_id,
c.country,
count(o.order_id) AS orders,
sum(p.amount) AS revenue,
o.order_date AS last_order
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN payments p ON p.order_id = o.order_id
GROUP BY c.customer_id, c.country
ORDER BY revenue DESC
LIMIT 10;
CLAUDE_DE_EOF
mkdir -p sql
cat > sql/customer_orders.sql <<'CLAUDE_DE_EOF'
-- Order history page: the 10 latest orders of one customer (runs on every page view)
SELECT order_id, order_date, status, total
FROM orders
WHERE customer_id = 4242
ORDER BY order_date DESC
LIMIT 10;
CLAUDE_DE_EOF
# ---------- setup ----------
psql -q -v ON_ERROR_STOP=1 -f db/schema.sql -f db/seed.sql
# ---------- step 1: Run the query from the Claude app ----------
psql -v ON_ERROR_STOP=1 -f sql/country_revenue.sql
# ---------- step 2: Run the broken top customers report ----------
psql -v ON_ERROR_STOP=1 -f sql/top_customers.sql # expected to fail
# ---------- step 3: Fix it with max(order_date) and run it again ----------
mkdir -p sql
cat > sql/top_customers.sql <<'CLAUDE_DE_EOF'
-- Top 10 customers by paid revenue
SELECT c.customer_id,
c.country,
count(o.order_id) AS orders,
sum(p.amount) AS revenue,
max(o.order_date) AS last_order
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN payments p ON p.order_id = o.order_id
GROUP BY c.customer_id, c.country
ORDER BY revenue DESC
LIMIT 10;
CLAUDE_DE_EOF
psql -v ON_ERROR_STOP=1 -f sql/top_customers.sql
# ---------- step 4: EXPLAIN ANALYZE the order history query (before) ----------
psql -c "EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT order_id, order_date, status, total FROM orders WHERE customer_id = 4242 ORDER BY order_date DESC LIMIT 10"
# ---------- step 5: Add the index as a migration ----------
mkdir -p db/migrations
cat > db/migrations/001_orders_customer_date_idx.sql <<'CLAUDE_DE_EOF'
-- 001: speed up the order history page (filter on customer_id, newest orders first)
-- CONCURRENTLY: does not block writes on the live table (cannot run inside a transaction)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_date
ON orders (customer_id, order_date DESC);
CLAUDE_DE_EOF
psql -v ON_ERROR_STOP=1 -f db/migrations/001_orders_customer_date_idx.sql
# ---------- step 6: EXPLAIN ANALYZE again (after) ----------
psql -c "EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT order_id, order_date, status, total FROM orders WHERE customer_id = 4242 ORDER BY order_date DESC LIMIT 10"
# ---------- step 7: Monthly growth with window functions ----------
mkdir -p sql
cat > sql/monthly_growth.sql <<'CLAUDE_DE_EOF'
-- Monthly paid revenue: previous month,
-- growth in percent and a running total
WITH monthly (month, revenue) AS (
SELECT date_trunc('month', paid_at)::date,
sum(amount)
FROM payments
GROUP BY 1
)
SELECT month,
revenue,
-- last months revenue
lag(revenue) OVER w AS prev_month,
-- change vs last month, in percent
round(100.0 * (revenue / lag(revenue) OVER w
- 1), 1) AS growth_pct,
-- running total
sum(revenue) OVER w AS running_total
FROM monthly
-- one named window, shared by all three
WINDOW w AS (ORDER BY month)
ORDER BY month;
CLAUDE_DE_EOF
psql -v ON_ERROR_STOP=1 -f sql/monthly_growth.sql
Part 4: Build a PySpark Pipeline with Claude Code
The project and its data are invented for teaching. Every file on this page is the real file from the demo project, and every output is the real output of the command above it, captured when the video was made (each step ran twice in a fresh folder and the outputs were compared). The Claude screens in the video are a simplified recreation for teaching. Always review what Claude changes and runs. Claude plans, features and limits are current as of November 2026; check the official Claude and Claude Code documentation before you rely on them. Part of the Claude for Data Engineers series on TechBrothersIT.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.