DEV Community: Lucas Braun

SQL window functions: what separates a mid-level from a senior developer

Lucas Braun — Fri, 26 Jun 2026 14:29:13 +0000

Most developers know SELECT, JOIN, and GROUP BY. Window functions are where the gap between mid and senior starts to show.

What makes window functions different

GROUP BY collapses rows. You lose the individual record — it becomes part of an aggregate.

Window functions don't collapse anything. They compute an aggregate over a set of rows while keeping every row intact. That's the core idea.

-- GROUP BY: one row per department
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Window function: every employee row, plus their department average
SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Same average calculation. Completely different result shape. Window functions let you ask "how does this row relate to the group?" — something GROUP BY simply can't do.

The anatomy of a window function

function_name() OVER (
    PARTITION BY col1, col2   -- defines the "group" (optional)
    ORDER BY col3             -- defines row order within the group
    ROWS BETWEEN ...          -- defines the frame (optional)
)

PARTITION BY — splits rows into independent windows. Omit it and the entire result set is one window.
ORDER BY — required for ranking and frame-sensitive functions. Changes the meaning of aggregates.
frame clause — defines which rows are "visible" to the function. Default varies by function.

Ranking functions

`ROW_NUMBER()`

Assigns a unique sequential number to each row within the partition. No ties — if two rows are equal, the order is arbitrary but distinct.

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;

Classic use case: get the top N rows per group without a subquery per group.

-- Top earner per department
SELECT * FROM (
    SELECT
        name,
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
) ranked
WHERE rn = 1;

`RANK()` vs `DENSE_RANK()`

Both handle ties, but differently:

RANK() — tied rows get the same rank, next rank skips. (1, 2, 2, 4)
DENSE_RANK() — tied rows get the same rank, next rank does not skip. (1, 2, 2, 3)

SELECT
    name,
    salary,
    RANK()       OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Use DENSE_RANK() when you want "2nd place" to actually mean second, not "tied for second, third place vacant."

Offset functions: LAG and LEAD

These let you access another row's value from the current row — without a self-join.

LAG(column, offset, default)  OVER (PARTITION BY ... ORDER BY ...)
LEAD(column, offset, default) OVER (PARTITION BY ... ORDER BY ...)

-- Month-over-month revenue change
SELECT
    month,
    revenue,
    LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS delta
FROM monthly_revenue;

-- Time between consecutive orders per user
SELECT
    user_id,
    order_date,
    LEAD(order_date) OVER (PARTITION BY user_id ORDER BY order_date) AS next_order,
    LEAD(order_date) OVER (PARTITION BY user_id ORDER BY order_date) - order_date AS days_until_next
FROM orders;

LAG/LEAD replace a category of self-joins that are painful to read and slow to execute. If you see a query joining a table to itself on id - 1 or date arithmetic, it's almost always a LAG in disguise.

Aggregate window functions

Any aggregate (SUM, AVG, COUNT, MIN, MAX) can be used as a window function. This unlocks running totals and moving averages without cursors or subqueries.

Running total

SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Running total reset per partition

SELECT
    user_id,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS user_running_total
FROM orders;

Moving average (last 7 days)

This is where the frame clause matters:

SELECT
    date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_revenue;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — the window includes the current row and the 6 rows before it. Classic 7-day rolling average.

ROWS vs RANGE

ROWS — physical rows. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means exactly 6 rows back.
RANGE — logical range based on the ORDER BY value. RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW means all rows within 6 days, regardless of row count.

For time series with gaps, RANGE is usually what you actually want.

-- ROWS: always exactly 6 rows back (may span more than 6 days if data is sparse)
AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- RANGE: all rows within the last 6 days (correct for sparse data)
AVG(revenue) OVER (ORDER BY date RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW)

`NTILE()`: bucketing rows

Divides rows into N equal-sized buckets. Useful for percentiles, quartiles, and cohort analysis.

-- Divide customers into 4 quartiles by total spend
SELECT
    customer_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile
FROM customer_spend;

Quartile 1 = top spenders. Combine with a CTE to filter or label each group.

`FIRST_VALUE()` and `LAST_VALUE()`

Return the first or last value in the window frame.

SELECT
    name,
    department,
    salary,
    FIRST_VALUE(name) OVER (
        PARTITION BY department ORDER BY salary DESC
    ) AS highest_paid_in_dept
FROM employees;

LAST_VALUE() has a trap: the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so "last" means the current row, not the end of the partition. Fix it explicitly:

LAST_VALUE(name) OVER (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Reading execution plans

Window functions aren't free. Know what to look for in EXPLAIN ANALYZE.

EXPLAIN ANALYZE
SELECT
    user_id,
    order_date,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date)
FROM orders;

Look for:

WindowAgg — the window function node. Normal.
Sort — a sort before WindowAgg. May be expensive on large tables.
Seq Scan vs Index Scan — if your PARTITION BY or ORDER BY columns aren't indexed, Postgres will sort the full table.

When to add an index

If the same PARTITION BY / ORDER BY combination appears in multiple queries:

CREATE INDEX idx_orders_user_date ON orders (user_id, order_date);

This lets Postgres avoid a full sort and use the index order directly.

When NOT to use window functions

Small datasets where a simple subquery is more readable
When you need to filter on the window result in the same query level — you can't WHERE rank = 1 directly; you need a CTE or subquery
When the frame logic gets complex enough that a CTE makes the intent clearer

Practical patterns worth bookmarking

Deduplicate keeping latest record

WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM events
)
SELECT * FROM ranked WHERE rn = 1;

Percent of total

SELECT
    department,
    SUM(salary) AS dept_total,
    SUM(salary) * 100.0 / SUM(SUM(salary)) OVER () AS pct_of_company
FROM employees
GROUP BY department;

Note: SUM(SUM(salary)) OVER () — a window function over an aggregate. This is valid SQL and very useful.

Gap detection between dates

SELECT
    user_id,
    order_date,
    LAG(order_date) OVER (PARTITION BY user_id ORDER BY order_date) AS prev_order,
    order_date - LAG(order_date) OVER (PARTITION BY user_id ORDER BY order_date) AS gap_days
FROM orders
WHERE gap_days > 30;  -- wrap in CTE to filter

Summary

Function	Use case
`ROW_NUMBER()`	Unique rank, top-N per group, deduplication
`RANK()` / `DENSE_RANK()`	Ranking with ties
`LAG()` / `LEAD()`	Compare current row to previous/next
`SUM() OVER`	Running totals
`AVG() OVER` + frame	Moving averages
`NTILE()`	Percentiles, quartiles, bucketing
`FIRST_VALUE()` / `LAST_VALUE()`	Boundary values within a partition

Window functions don't replace every query pattern — but they replace the ugly ones. Self-joins, correlated subqueries, and cursor-based loops are often a window function with cleaner syntax and a better execution plan.

Learn the frame clause. It's the thing most tutorials skip, and it's where the real power is.

What's your most-used window function pattern in production? Drop it in the comments.

Tags: sql postgres backend database

AsyncIO in production: event loop, tasks, and the traps no one warns you about

Lucas Braun — Fri, 19 Jun 2026 14:56:10 +0000

You've shipped async code. It works in dev. Then production hits — and it hangs, leaks memory, or silently swallows exceptions. Here's what actually matters.

The mental model most tutorials skip

When you write async def and await, you're not getting parallelism — you're getting cooperative multitasking. The event loop runs one coroutine at a time. Every await is a voluntary yield: "I'm waiting for I/O, go run something else."

This means:

CPU-bound work blocks the entire loop — no other coroutine runs while you're crunching numbers
A single time.sleep() inside an async function freezes everything
"Async" doesn't mean "fast" — it means "efficient while waiting"

import asyncio
import time

async def bad():
    time.sleep(2)  # blocks the entire event loop for 2 seconds
    return "done"

async def good():
    await asyncio.sleep(2)  # yields control — other tasks run
    return "done"

Internalize this and half the async bugs you'll ever face become obvious.

asyncio.create_task() vs asyncio.gather() vs TaskGroup

Three ways to run coroutines concurrently. They are not interchangeable.

`asyncio.gather()`

Runs coroutines concurrently and collects results. By default, if one raises, it cancels nothing — the others keep running. With return_exceptions=True, exceptions come back as values.

results = await asyncio.gather(
    fetch_user(user_id),
    fetch_orders(user_id),
    fetch_preferences(user_id),
    return_exceptions=True,
)

for r in results:
    if isinstance(r, Exception):
        logger.error("subtask failed", exc_info=r)

The problem: with return_exceptions=False (the default), a single failure raises immediately and the remaining coroutines are not awaited — they're abandoned, which can cause resource leaks.

`asyncio.create_task()`

Schedules a coroutine as a background task. It starts running immediately, independently of what you await next.

task = asyncio.create_task(send_notification(user_id))
# do other work here
await task  # or don't — and risk losing the exception

The trap: if you never await a task and it raises, the exception disappears silently. You'll see a Task exception was never retrieved warning in logs — easy to miss.

Always keep a reference to tasks you create. The event loop only holds a weak reference.

# wrong — task may be garbage collected mid-flight
asyncio.create_task(background_job())

# right — keep a strong reference
_background_tasks: set[asyncio.Task] = set()

def fire_and_forget(coro):
    task = asyncio.create_task(coro)
    _background_tasks.add(task)
    task.add_done_callback(_background_tasks.discard)

`asyncio.TaskGroup` (Python 3.11+)

The modern, correct way to run structured concurrent tasks. If any task fails, all others are cancelled. No exceptions get swallowed.

async def fetch_all(user_id: int):
    async with asyncio.TaskGroup() as tg:
        user_task = tg.create_task(fetch_user(user_id))
        orders_task = tg.create_task(fetch_orders(user_id))

    # both tasks are done here — or an ExceptionGroup was raised
    return user_task.result(), orders_task.result()

Use TaskGroup for new code. Use gather only when you need return_exceptions=True semantics or are on Python < 3.11.

Timeouts: don't trust your dependencies

External services lie. They accept your connection and then stop responding. Without timeouts, you get coroutines waiting forever, exhausting your connection pool.

`asyncio.timeout()` (Python 3.11+)

async def fetch_with_timeout(url: str) -> dict:
    async with asyncio.timeout(5.0):
        async with httpx.AsyncClient() as client:
            response = await client.get(url)
            return response.json()

Raises asyncio.TimeoutError if the block takes more than 5 seconds. Clean, readable, composable.

`asyncio.wait_for()` for older Python

try:
    result = await asyncio.wait_for(fetch_data(), timeout=5.0)
except asyncio.TimeoutError:
    logger.warning("fetch_data timed out")
    raise

The trap: cancellation and cleanup

When a timeout fires, the inner coroutine receives a CancelledError. If it catches Exception broadly and swallows it, the cancellation is suppressed — your timeout does nothing.

# wrong — suppresses CancelledError
async def bad_fetch():
    try:
        return await httpx_client.get(url)
    except Exception:
        return None  # this catches CancelledError too

# right — let CancelledError propagate
async def good_fetch():
    try:
        return await httpx_client.get(url)
    except httpx.HTTPError as e:
        logger.error("HTTP error", exc_info=e)
        return None
    # CancelledError is NOT caught here — it propagates correctly

`asyncio.shield()`: protecting critical work from cancellation

Sometimes a task gets cancelled (timeout, parent TaskGroup failure) but you need a specific operation to finish — like writing to a database or sending a notification.

async def save_and_notify(data: dict):
    # Even if this coroutine is cancelled, save_to_db will complete
    await asyncio.shield(save_to_db(data))
    await send_notification(data["user_id"])  # this CAN be cancelled

Important: shield() does not prevent the outer coroutine from being cancelled. It only protects the inner task from receiving the cancellation signal. The outer coroutine still gets CancelledError — you need to handle it.

Use shield() sparingly. If you're shielding a lot, your cancellation design is probably wrong.

Debugging stuck coroutines in production

Enable slow callback logging

The event loop logs a warning when a callback takes too long. Set the threshold:

import asyncio
import logging

logging.basicConfig(level=logging.DEBUG)

loop = asyncio.get_event_loop()
loop.slow_callback_duration = 0.1  # warn if any callback blocks > 100ms

This catches the most common issue: a sync library call or CPU-bound operation blocking the loop.

Dump all running tasks

When your service hangs, dump what's running:

import asyncio

def dump_tasks():
    for task in asyncio.all_tasks():
        print(task.get_name())
        task.print_stack()

In FastAPI, wire this to a debug endpoint or a signal handler:

import signal

def setup_debug_signal():
    def handler(sig, frame):
        dump_tasks()
    signal.signal(signal.SIGUSR1, handler)

# kill -USR1 <pid> to trigger

`asyncio.Runner` for scripts (Python 3.11+)

Instead of asyncio.run(), use Runner for more control:

with asyncio.Runner(debug=True) as runner:
    runner.run(main())

Debug mode enables:

Slow callback warnings
Coroutine origin tracking (shows where each task was created)
Unawaited coroutine detection

The FastAPI-specific traps

Sync dependencies in async routes

FastAPI runs sync dependencies in a thread pool. But if you mix sync and async carelessly, you can block the event loop.

# wrong — sync DB call inside async route blocks the loop
@app.get("/users/{id}")
async def get_user(id: int, db: Session = Depends(get_db)):
    return db.query(User).filter(User.id == id).first()

# right — use async session with SQLAlchemy async
@app.get("/users/{id}")
async def get_user(id: int, db: AsyncSession = Depends(get_async_db)):
    result = await db.execute(select(User).where(User.id == id))
    return result.scalar_one_or_none()

Async generator leaks

FastAPI uses async generators for dependencies (the yield pattern). If the dependency is never properly closed — due to a timeout or cancellation — cleanup code after yield doesn't run.

async def get_db():
    async with AsyncSession(engine) as session:
        try:
            yield session
            await session.commit()
        except Exception:
            await session.rollback()
            raise
        # this finally block ALWAYS runs — even on cancellation
        finally:
            await session.close()

Always use try/finally in async generator dependencies. Never assume the happy path.

Summary

Scenario	Use
Run N coroutines, collect all results	`asyncio.gather()` with `return_exceptions=True`
Run N coroutines, fail fast on first error	`asyncio.TaskGroup`
Fire-and-forget background task	`create_task()` + strong reference
Timeout an operation	`asyncio.timeout()` or `wait_for()`
Protect critical work from cancellation	`asyncio.shield()`
Debug a hung event loop	`all_tasks()` + signal handler

AsyncIO's model is simple once you internalize it. The bugs don't come from complexity — they come from assuming it works like threading, or from libraries that weren't built for async. Know your primitives, handle cancellation explicitly, and always keep a reference to your tasks.

Have a war story about async bugs in production? Drop it in the comments — the more specific, the better.

Tags: python asyncio fastapi backend productivity

Why Python is the best first language (and when it isn't)

Lucas Braun — Fri, 17 Apr 2026 17:54:40 +0000

Search "what language should I learn first" and 90% of the answers will say Python.

That's not a conspiracy, not hype, not a course sponsorship. There's real reason behind it. But there's also context those answers ignore — and ignoring context is what makes a lot of people start with the right language for the wrong goal.

So let's be honest about both sides.

Why Python makes sense as a first language

1. The syntax doesn't fight you

Most languages have ceremonies that make no sense when you're starting out. Declaring variable types, semicolons at the end of every line, curly braces everywhere.

Python removes most of that. Compare "hello, world" in Java:

public class Main {
    public static void main(String[] args) {
        System.out.println("Hello, world!");
    }
}

And in Python:

print("Hello, world!")

This isn't just aesthetics. When you're learning programming logic, every unfamiliar syntax element is one more thing to worry about. Python lets you focus on what matters: the reasoning.

2. You can build useful things fast

With Python, in a few weeks you can already:

Automate repetitive tasks on your computer
Analyze a data spreadsheet
Make a request to an API
Write a script that renames 500 files at once

That's motivation. And motivation is what keeps beginners going when things get hard.

3. The industry actually uses Python

Python isn't a toy language you learn and throw away. It powers a big chunk of:

Data science and ML: pandas, numpy, scikit-learn, TensorFlow, PyTorch
Automation and DevOps: scripts, pipelines, infrastructure tools
Web backend: Django, FastAPI, Flask
Security: pentest scripts, malware analysis

If you learn Python well, there are real jobs waiting on the other side.

4. The community is huge (and generous)

Got an error in your code? There's a Stack Overflow answer for it. Want to do something specific? There's a library for that. Want to learn for free? There's quality material at every level.

A large community means less time stuck on a problem with no way out.

When Python is not the best choice

Now for the part tutorials skip.

If you want mobile development

Python has no meaningful presence in mobile app development. If your goal is to build Android or iOS apps, you'll want:

Kotlin (native Android)
Swift (native iOS)
JavaScript/TypeScript with React Native (cross-platform)
Dart with Flutter (cross-platform)

Learning Python first will slow you down. Go straight to where you want to be.

If you want frontend web development

Python runs on the server, not in the browser. If you want to build interfaces, buttons, animations, the visual side of a website — that's JavaScript and HTML/CSS territory.

Python might complement your stack later on the backend, but if the goal is frontend, it's not where you start.

If you want to build games

There's Pygame, but honestly? It's not what the industry uses. If games are the goal:

C# with Unity is the most practical path for the indie market
GDScript or C# with Godot if you prefer open source
C++ if you have ambitions to work at large studios

If extreme performance is the requirement

Python is slow compared to compiled languages. For embedded systems, drivers, game engines, or anything where every millisecond counts, you'll want C, C++, or Rust.

That doesn't mean Python is useless in those contexts — sometimes it's used as glue between components. But as the primary language of a performance-critical system, it's not the ideal choice.

The question that actually matters

Before choosing a language, answer one question:

What do you want to build?

Goal	Where to start
Data science, AI, automation	Python
Web development (visual)	JavaScript + HTML/CSS
Mobile apps	Kotlin, Swift, or Flutter
Web backend	Python, JavaScript (Node), Go
Games	C# (Unity) or GDScript (Godot)
Low-level systems	C or C++
Offensive security	Python + Bash

If you still don't know what you want to build, then Python is the best bet. It's versatile enough to help you figure out what interests you without locking you into a niche.

One thing that matters less than it seems

A lot of people get stuck on the language choice because they think it's a permanent decision. It's not.

Your first language is there to teach you how to think like a programmer: loops, conditionals, functions, data structures, debugging. Those concepts exist in every language. Once you learn the logic well in Python, picking up JavaScript or Kotlin later becomes much easier.

The language is the vehicle. The reasoning is what you actually learn.

Python is an excellent first language for most people. But "most" isn't "everyone". Knowing what you want to build matters more than following the default recommendation on autopilot.

Git beyond push: the commands nobody taught you

Lucas Braun — Fri, 10 Apr 2026 13:30:28 +0000

Everyone learns Git the same way: git init, git add ., git commit -m "first commit", git push.

And then just... stops there.

The problem is that Git has a huge set of commands that make a real difference day-to-day — and almost no beginner tutorial ever mentions them. You only find out about them when something breaks, at 11pm, with an open pull request.

This article is here to change that.

1. `git stash` — Git's secret pocket

You're in the middle of a feature, the code is half-done, and you get a message: "there's an urgent bug to fix on main". What do you do?

If you try git checkout like that, Git will complain. If you make a commit, you'll pollute your history with a "WIP: saving to switch branches".

The solution is stash:

git stash

This saves all your uncommitted changes to a temporary place and leaves your working directory clean. Fix the bug, come back, and restore:

git stash pop

Want to see what you have saved?

git stash list

You can have multiple stashes and give them names:

git stash push -m "login feature halfway done"

2. `git log --oneline` — because the default log is terrible

Ever opened a git log and stared at a massive wall of text? There's a much better way:

git log --oneline

Output:

a3f1c2e feat: add JWT authentication
9b2d441 fix: correct form validation
3c8e109 chore: update dependencies

Clean, readable, one commit per line.

Want to see branches too?

git log --oneline --graph --all

This draws a visual tree of your branches in the terminal. Looks like something from a hacker movie, but it's just Git.

3. `git diff` — before committing, see what you actually changed

You know that moment when you type git add . without even checking what changed? Yeah, everyone does it. But the right way is to review first:

git diff

This shows line by line what changed in files that haven't been staged yet.

Already ran git add and want to review before committing?

git diff --staged

Want to compare two specific commits?

git diff abc1234 def5678

Reviewing your diff becomes a habit once you stop accidentally committing garbage.

4. `git commit --amend` — fixing your last commit

Committed with the wrong message? Forgot to include a file? You don't need to create a new commit just for that:

git commit --amend

This opens your editor with the last commit message for you to edit. Save, close, done.

Want to change the message directly without opening the editor?

git commit --amend -m "feat: add JWT authentication"

Want to include a forgotten file?

git add forgotten-file.py
git commit --amend --no-edit

⚠️ Warning: only use --amend on commits that haven't been pushed to the remote yet. If you've already pushed, you'll run into trouble pushing again.

5. `git restore` — undoing changes without drama

You changed a file and regretted it. Want to go back to how it was at the last commit?

git restore filename.py

This discards all local changes in that file. No commit, no hassle.

Want to undo a git add (unstage a file)?

git restore --staged filename.py

This doesn't delete your changes, it just removes the file from the staging area. Your edits are still there.

6. `git revert` — undoing a commit without rewriting history

Made a commit you shouldn't have? The temptation is to use git reset, but there's a problem: it rewrites history, which can cause a mess for teams.

git revert is safer: it creates a new commit that undoes the changes from a previous commit:

git revert abc1234

The history stays intact, you just add a new commit that reverts the damage. Perfect for team environments.

7. `git cherry-pick` — taking only what you need

Imagine you have a bugfix on a feature branch, but you need to apply that fix to main right now, without merging everything.

cherry-pick takes a specific commit and applies it to another branch:

git cherry-pick abc1234

Switch to the target branch, run this command with the hash of the commit you want, and done. Only that commit gets copied over.

Bonus: aliases that will save you time

Tired of typing git log --oneline --graph --all every time? Create an alias:

git config --global alias.tree "log --oneline --graph --all"

Now just type:

git tree

You can create aliases for any command. Some I use all the time:

git config --global alias.st "status"
git config --global alias.co "checkout"
git config --global alias.unstage "restore --staged"

Summary

Command	When to use
`git stash`	Save work in progress to switch context
`git log --oneline`	View history in a readable way
`git diff --staged`	Review what's going into the next commit
`git commit --amend`	Fix the last commit (message or files)
`git restore`	Undo changes in files
`git revert`	Safely undo a commit in a team environment
`git cherry-pick`	Apply a specific commit to another branch

These commands aren't advanced — they're essential. They're what separates someone who "uses Git" from someone who actually works well with it.

DEV Community: Lucas Braun

SQL window functions: what separates a mid-level from a senior developer

What makes window functions different

The anatomy of a window function

Ranking functions

ROW_NUMBER()

RANK() vs DENSE_RANK()

Offset functions: LAG and LEAD

Aggregate window functions

Running total

Running total reset per partition

Moving average (last 7 days)

ROWS vs RANGE

NTILE(): bucketing rows

FIRST_VALUE() and LAST_VALUE()

Reading execution plans

When to add an index

When NOT to use window functions

Practical patterns worth bookmarking

Deduplicate keeping latest record

Percent of total

Gap detection between dates

Summary

AsyncIO in production: event loop, tasks, and the traps no one warns you about

The mental model most tutorials skip

asyncio.create_task() vs asyncio.gather() vs TaskGroup

asyncio.gather()

asyncio.create_task()

asyncio.TaskGroup (Python 3.11+)

Timeouts: don't trust your dependencies

asyncio.timeout() (Python 3.11+)

asyncio.wait_for() for older Python

The trap: cancellation and cleanup

asyncio.shield(): protecting critical work from cancellation

Debugging stuck coroutines in production

Enable slow callback logging

Dump all running tasks

asyncio.Runner for scripts (Python 3.11+)

The FastAPI-specific traps

Sync dependencies in async routes

Async generator leaks

Summary

Why Python is the best first language (and when it isn't)

Why Python makes sense as a first language

1. The syntax doesn't fight you

2. You can build useful things fast

3. The industry actually uses Python

4. The community is huge (and generous)

When Python is not the best choice

If you want mobile development

If you want frontend web development

If you want to build games

If extreme performance is the requirement

The question that actually matters

One thing that matters less than it seems

Git beyond push: the commands nobody taught you

1. git stash — Git's secret pocket

2. git log --oneline — because the default log is terrible

3. git diff — before committing, see what you actually changed

4. git commit --amend — fixing your last commit

5. git restore — undoing changes without drama

6. git revert — undoing a commit without rewriting history

7. git cherry-pick — taking only what you need

Bonus: aliases that will save you time

Summary

`ROW_NUMBER()`

`RANK()` vs `DENSE_RANK()`

`NTILE()`: bucketing rows

`FIRST_VALUE()` and `LAST_VALUE()`

`asyncio.gather()`

`asyncio.create_task()`

`asyncio.TaskGroup` (Python 3.11+)

`asyncio.timeout()` (Python 3.11+)

`asyncio.wait_for()` for older Python

`asyncio.shield()`: protecting critical work from cancellation

`asyncio.Runner` for scripts (Python 3.11+)

1. `git stash` — Git's secret pocket

2. `git log --oneline` — because the default log is terrible

3. `git diff` — before committing, see what you actually changed

4. `git commit --amend` — fixing your last commit

5. `git restore` — undoing changes without drama

6. `git revert` — undoing a commit without rewriting history

7. `git cherry-pick` — taking only what you need