DEV Community: Neha Christina

CTEs vs Subqueries in SQL: Which One Should You Use?

Neha Christina — Thu, 02 Apr 2026 10:44:25 +0000

If you've written SQL for more than a week, you've hit this question.

Do I use a CTE or a subquery here?

Both can solve the same problem. Both produce the same result. So which one is actually better?

The honest answer: it depends. But there are clear rules for when to use each — and once you know them, you'll never second-guess yourself again.

What is a Subquery?

A subquery is a query nested inside another query. It runs inline, right where it's written.

SELECT name, salary
FROM employees
WHERE salary > (
  -- This is the subquery
  SELECT AVG(salary)
  FROM employees
);

The inner SELECT AVG(salary) runs first, produces a single value, and the outer query uses that value in its WHERE clause.

Subqueries can live in SELECT, FROM, WHERE, and HAVING clauses.

Pros: Quick to write for simple filters. No extra syntax needed.

Cons: Gets hard to read when nested more than one level deep. Can't reuse the result elsewhere in the same query.

What is a CTE?

A CTE (Common Table Expression) is a named temporary result set defined at the top of your query using WITH … AS (). You reference it by name below — just like a real table.

-- Define the CTE first
WITH avg_salary AS (
  SELECT AVG(salary) AS avg_sal
  FROM employees
)

-- Now use it like a table
SELECT e.name, e.salary
FROM employees e
JOIN avg_salary a ON e.salary > a.avg_sal;

The CTE is defined once at the top and can be referenced multiple times in the main query.

Pros: Very readable — complex logic is broken into named steps. Can be referenced more than once within the same query.

Cons: Slightly more verbose. Not all databases support recursive CTEs.

Same Query, Two Ways

Here's the same problem — find employees earning above the average salary — written both ways:

Subquery version:

SELECT name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);

CTE version:

WITH avg_sal AS (
  SELECT AVG(salary) AS avg
  FROM employees
)
SELECT e.name, e.salary
FROM employees e
JOIN avg_sal a ON e.salary > a.avg;

Same result. Different readability. The CTE version makes the intent clearer — especially as queries get more complex.

When to Use a Subquery

Use a subquery when the logic is simple and short, you only need it in one place, and you're filtering in WHERE or HAVING. For quick one-liners, subqueries are perfectly fine.

When to Use a CTE

Use a CTE when you need to reference the result more than once, the query is complex and needs to be readable, or you're building step-by-step transformations where each step builds on the last.

CTEs are especially powerful when chained together:

WITH
step_one AS (
  SELECT dept, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept
),
step_two AS (
  SELECT dept, avg_sal
  FROM step_one
  WHERE avg_sal > 70000
)
SELECT * FROM step_two;

Each CTE becomes a building block. Much easier to debug than deeply nested subqueries.

Do CTEs Run Faster?

This is the question most people get wrong.

In most modern databases — including Snowflake and BigQuery — the query optimiser treats CTEs and subqueries almost identically. Neither is consistently faster.

The exception: older versions of Redshift and PostgreSQL sometimes materialise CTEs (compute them separately and store the result), which can make CTEs slower for complex queries. If you're on one of these, test both and measure.

The real rule: optimise for readability first. Only swap to subqueries for performance if you've actually measured a difference.

The Cheat Sheet

	Subquery	CTE
Syntax	Inline	`WITH … AS ()`
Readability	Gets messy	Very readable
Reuse in query?	No	Yes
Performance	Same	Same*
Recursive?	No	Yes
Best for	Simple filters	Complex logic

*Performance varies by database — always test.

Note on reusability: A CTE can be referenced multiple times within the single query it's defined in. It does not persist across queries — for that you'd need a temporary table or a view.

The Simple Decision Rule

If the logic fits in 2–3 lines and you only need it once → subquery.

If the logic is complex, reused, or needs to be understood by someone else → CTE.

When in doubt, use a CTE. Readability is worth more than brevity in team environments.

Which do YOU prefer — CTEs or subqueries? Drop a comment below 👇

Follow me on Instagram at https://www.instagram.com/techqueen.codes for visual SQL, Python and Snowflake tips every week 💙

5 Python Built-ins You’re Not Using (But Should Be)

Neha Christina — Fri, 27 Mar 2026 18:36:43 +0000

You don’t need a library. You don’t need to pip install anything. These five tools are already sitting inside Python, waiting for you to use them.

Most junior developers don’t know they exist. Senior developers reach for them every single day.

Let’s fix that.

1. zip()

Loop over multiple lists at the same time.

How most beginners do it:

names = ['Alice', 'Bob', 'Carol']
scores = [95, 87, 92]

for i in range(len(names)):
    print(names[i], scores[i])

How you should do it:

for name, score in zip(names, scores):
    print(name, score)

zip() pairs up elements from two (or more) iterables and lets you loop over them together. It stops when the shortest one runs out.

You can also use it to combine lists into a dictionary:

name_score = dict(zip(names, scores))
# {'Alice': 95, 'Bob': 87, 'Carol': 92}

2. any() and all()

Check conditions across a list without writing a loop.

The verbose way:

scores = [85, 92, 78, 95]
has_pass = False
for s in scores:
    if s >= 80:
        has_pass = True
        break

The clean way:

has_pass = any(s >= 80 for s in scores)
all_pass = all(s >= 80 for s in scores)

any() returns True if at least one item meets the condition. all() returns True only if every item does. Both short-circuit — they stop checking as soon as the answer is known, which makes them fast.

3. enumerate()

Get the index AND value without range(len()).

The clunky version:

fruits = ['apple', 'banana', 'cherry']
for i in range(len(fruits)):
    print(i, fruits[i])

The Pythonic version:

for i, fruit in enumerate(fruits):
    print(i, fruit)

Want to start counting from 1 instead of 0?

for i, fruit in enumerate(fruits, start=1):
    print(i, fruit)

range(len()) is considered a code smell in Python. If you find yourself writing it, reach for enumerate() instead.

4. map()

Apply a function to every item in a list.

The loop version:

names = ['alice', 'bob', 'carol']
capitalised = []
for name in names:
    capitalised.append(name.upper())

With map():

capitalised = list(map(str.upper, names))

Or with a lambda for custom logic:

doubled = list(map(lambda x: x * 2, numbers))

map() is great for simple, single-function transformations. For more complex logic involving conditions or multiple steps, a list comprehension is usually more readable:

capitalised = [name.upper() for name in names]

Both are valid — pick whichever reads more clearly for your use case.

5. collections

Counter, defaultdict, deque — all built in.

The manual counting approach:

words = ['cat', 'dog', 'cat', 'bird', 'cat']
counts = {}
for word in words:
    if word in counts:
        counts[word] += 1
    else:
        counts[word] = 1

With Counter:

from collections import Counter

counts = Counter(words)
# Counter({'cat': 3, 'dog': 1, 'bird': 1})

top_2 = counts.most_common(2)
# [('cat', 3), ('dog', 1)]

collections also gives you:

defaultdict — like a regular dict, but never raises a KeyError. Automatically creates a default value for missing keys.

from collections import defaultdict
d = defaultdict(list)
d['missing'].append(1)  # No KeyError!

deque — a double-ended queue. Much faster than a list for appending or popping from both ends.

namedtuple — a tuple where you can access items by name instead of index.

The Cheat Sheet

Built-in	What it does	When to use it
`zip()`	Pairs up multiple iterables	Looping over two lists together
`any()`	True if at least one item passes	Checking if any condition is met
`all()`	True if every item passes	Validating a whole list
`enumerate()`	Gives index + value	When you need both in a loop
`map()`	Applies a function to each item	Simple transformations
`Counter`	Counts occurrences	Frequency analysis
`defaultdict`	Dict with default values	Avoiding KeyErrors

The Key Takeaway

None of these require installing anything. They’re all part of Python’s standard library, designed specifically for the problems you solve every day.

The difference between a junior and senior Python developer often isn’t the complex stuff — it’s knowing these small tools exist and reaching for them instead of writing loops from scratch.

Which one did you NOT know about before reading this? Comment below 👇

Follow me on Instagram at https://www.instagram.com/techqueen.codes for visual SQL, Python and Snowflake tips every week 💚

A Day in the Life of a Data Engineer (Real Talk, No Filter)

Neha Christina — Tue, 24 Mar 2026 11:15:14 +0000

If you've ever wondered what data engineers actually do all day — this one's for you.

Not the LinkedIn version. The real version.

I've been in tech for 10+ years and I still get asked "so what do you actually do?" at family dinners. This post is my answer.

9:00 AM — Standup & Morning Triage

First thing I do is not open Slack.

I open my monitoring dashboard.

Most pipeline failures happen overnight while nobody's watching. If something broke at 3am, I want to know before the business analyst in another timezone notices their dashboard is showing yesterday's data.

Once I've checked the alerts I jump into standup. Ours is 15 minutes max. What did I do yesterday, what am I doing today, what's blocking me. That's it.

Then I triage tickets. Some days I have three things to do. Some days I have fifteen. Prioritising is a skill they don't teach you in bootcamp but you learn fast.

9:30 AM — Pipeline Review

This is the unglamorous part of the job that nobody talks about.

Every morning I check whether last night's ETL jobs completed successfully. Did the data land in Snowflake? Are the row counts what we'd expect? Did any data quality checks fail?

If something broke, I investigate. This means reading logs, checking error messages, and tracing back through the pipeline to find where it fell over. Sometimes it's a network timeout. Sometimes it's a schema change upstream that nobody told us about. Sometimes it's my own code from two weeks ago coming back to bite me.

Most pipeline fires happen overnight. Mornings are for damage control before anyone notices.

11:00 AM — Deep Work (Headphones On)

This is my favourite part of the day.

I block this time aggressively. No meetings if I can help it. Headphones on. Do Not Disturb on. This is when actual building happens.

What does building look like?

Writing Python ETL scripts. Reading data from an API, transforming it, loading it into Snowflake. Handling edge cases. Writing tests so future-me doesn't break it accidentally.

Building SQL transformations. Using dbt to model data in the warehouse — turning raw event data into clean, business-friendly tables that analysts can actually use.

Debugging. Always debugging. Something that worked last week mysteriously doesn't work today. This is the job.

12:30 PM — Code Review

Before I eat lunch I try to clear my code review queue.

Code review is one of the most valuable things a senior engineer can do for a team. Reading someone else's PR carefully, leaving a thoughtful comment, catching a bug before it hits production — that's real impact.

I also get my own code reviewed. Fresh eyes catch things you're blind to after staring at the same function for two hours.

2:00 PM — Stakeholder Meetings

Yes, data engineers go to meetings. A lot of them.

The afternoon tends to be meeting-heavy. This might be a sync with the data analytics team to tell them a new dataset is ready. It might be a session with a product manager to understand the business requirements for a new pipeline. It might be a cross-team call about an upcoming data model change that'll affect three different teams.

The best data engineers I've worked with are great communicators. They can explain a technical concept to a non-technical stakeholder without talking down to them. That skill is worth as much as knowing Python.

3:30 PM — Documentation & Mentoring

Nobody loves writing documentation but everyone loves finding it when they need it.

I try to keep a rule: if I build it, I document it. What does it do, where does the data come from, what should you do if it breaks. Future me and my teammates will thank present me.

This slot also tends to be when junior engineers come to me with questions. Debugging sessions, code reviews, architecture questions. I was that junior engineer once. Paying it forward matters.

4:00 PM — Wrap Up

The last hour is for closing out.

I look at my ticket list. What did I actually complete today? What's moving to tomorrow? Are there any overnight jobs I need to leave notes about?

I clear my Slack backlog — not the other way around. Slack is not a real-time obligation. I check it a few times a day, not constantly.

4:30 PM — Learning Time (Protected)

This is the 30 minutes most people skip and then wonder why they feel stuck.

I keep this time for growth. Reading a technical blog post. Watching a conference talk. Experimenting with a new Snowflake feature. Trying out a new Python library.

The data engineering landscape changes fast. If you're not learning you're falling behind.

The Tech Stack

Here's what I actually use every day:

Snowflake — cloud data warehouse, where most of our data lives and gets queried.

Python — ETL scripts, data transformation, automation, anything that needs logic.

GitLab — CI/CD pipelines, version control and deployments. Every pipeline change is tracked, reviewed and deployed through GitLab. (You'll see Apache Airflow in a lot of job listings for this role — GitLab is what we use at my company, but Airflow is worth learning too.)

Git / GitHub — version control for everything. Every change tracked, every deployment reviewed.

dbt — transforms raw data inside Snowflake using SQL. Makes data modelling collaborative and testable.

Slack — where everything actually happens. For better or worse.

The Honest Truth Nobody Tells You

60% of the job is debugging pipelines that worked yesterday. The other 40% is writing the pipelines that will break tomorrow.

Communication skills matter as much as coding skills. You can write the most elegant Python in the world but if you can't explain your data model to a product manager, you'll be invisible.

You'll never stop learning. The tools change every year. That's frustrating and exciting at the same time.

Boring days are good days. A day where nothing breaks, all pipelines run, and you shipped one clean PR is a successful day. Don't let anyone tell you otherwise.

Want to Become a Data Engineer?

Start with SQL. Then learn Python. Then learn one cloud data warehouse — BigQuery has the most generous free tier if you want to experiment without spending money.

The path is clearer than it looks from the outside.

Drop a 🙋 in the comments if you're on your way in — I'd love to know where you're starting from 👇

Follow me on Instagram at https://www.instagram.com/techqueen.codes for visual SQL, Python and Snowflake tips every week 💙

Snowflake vs Redshift vs BigQuery: Which One Should You Use?

Neha Christina — Sat, 21 Mar 2026 03:44:27 +0000

If you’ve ever Googled “which cloud data warehouse should I use” and ended up more confused than when you started — this post is for you.

Snowflake, Redshift, and BigQuery are the three biggest names in cloud data warehousing right now. They all do similar things. They all use SQL. They all run in the cloud. So how do you choose?

The answer depends on your situation. Let’s break it down.

What They All Have in Common

Before the differences, here’s what all three share:

They store massive amounts of data — we’re talking billions of rows — and let you query it with standard SQL. They all run entirely in the cloud, so there are no servers to buy or maintain. And they’re all used by major companies at scale.

The differences come down to three things: cost model, ecosystem, and who they’re built for.

❄️ Snowflake

The cloud-agnostic powerhouse.

Snowflake is unique because it runs on AWS, Azure, AND Google Cloud. No other major data warehouse does this. That means if your company uses multiple clouds — or might switch clouds in the future — Snowflake gives you flexibility nobody else does.

What makes Snowflake stand out:

Separation of storage and compute. This is Snowflake’s key architectural insight. Storage and compute are completely independent, so you can scale each one separately and only pay for what you actually use.

Multi-cluster warehouses. 100 analysts running queries at the same time? Each gets their own compute cluster. No one slows anyone else down.

Best-in-class data sharing. You can share live data with partners or other teams without copying it. This is genuinely impressive and something the other two struggle to match.

Time Travel. Accidentally deleted a table? You can recover it — up to 90 days back depending on your plan. This has saved countless data teams from disaster.

Zero-Copy Cloning. Clone an entire database instantly with no extra storage cost. Perfect for test environments.

Pricing: Credit-based, per second of compute. Easy to control once you understand it, but can surprise you if you leave warehouses running.

Best for: Teams that work across multiple clouds, organisations that need to share data externally, and companies that want maximum flexibility.

🔴 Redshift

The AWS native. Powerful if you’re already there.

Amazon Redshift is AWS’s data warehouse, and if your company is already deep in the AWS ecosystem, it’s a natural fit. The integration with S3, Lambda, Glue, Kinesis, and the rest of the AWS stack is seamless in a way that Snowflake and BigQuery simply can’t replicate.

What makes Redshift stand out:

Deep AWS integration. If you’re already using S3 for storage, IAM for access control, or Kinesis for streaming — Redshift plugs in with minimal friction.

Redshift Serverless. The newer serverless option means you don’t have to manage clusters anymore. It scales automatically and you pay only for what you use.

Strong performance for large structured datasets. Redshift uses columnar storage and is highly optimised for analytical queries across large volumes of structured data.

Enterprise security. Deep IAM integration, VPC support, encryption at rest and in transit. If your company has strict security requirements, Redshift ticks all the boxes.

Pricing: Traditionally priced per hour for provisioned clusters, which can get expensive at scale. Redshift Serverless is more flexible but costs can still add up. Reserved instances help significantly if you have predictable workloads.

Best for: Companies already deeply invested in AWS who want tight integration with the rest of their AWS infrastructure.

🔵 BigQuery

Google’s beast. Serverless and stupidly fast.

BigQuery is Google Cloud’s data warehouse, and it’s genuinely impressive. It’s the most truly serverless of the three — there’s no infrastructure to think about whatsoever. You just query data and Google handles everything else.

What makes BigQuery stand out:

Truly serverless. No clusters, no warehouses, no infrastructure decisions. Just write SQL and run it.

Generous free tier. 10GB of storage and 1TB of query processing free every month. This makes it the best option for learning or for small projects where you don’t want to spend anything.

BigQuery ML. You can train and run machine learning models using SQL — no Python required. This is genuinely unique and incredibly powerful for teams that want ML without a dedicated data science team.

Looker Studio integration. Connects directly to Google’s BI and visualisation tools with zero setup.

Pricing: Pay per query based on data scanned. This sounds great until someone writes a poorly optimised query on a huge table and gets a surprise bill. Always use LIMIT and partition your tables.

Best for: GCP-native teams, data science and ML workloads, anyone who wants to start for free, and teams already using Google Workspace or Looker.

The Cheat Sheet

	❄️ Snowflake	🔴 Redshift	🔵 BigQuery
Cloud	Any (AWS/Azure/GCP)	AWS only	GCP only
Pricing	Per second	Per hour	Per query
Free tier	30-day trial	2 months	Always free (1TB/mo)
Setup	Easy	Medium	Very easy
ML built-in	No	Limited	Yes
Data sharing	Best in class	Limited	Good

Which One Should You Pick?

Pick Snowflake if you work across multiple clouds, your team needs to share data externally, or you want maximum long-term flexibility.

Pick Redshift if your company is already all-in on AWS and you need tight integration with the rest of the AWS ecosystem.

Pick BigQuery if you’re on GCP, you want built-in ML capabilities, or you want to start completely free.

The Honest Take

If you’re just starting out and want to learn, BigQuery is the easiest entry point — free tier, no setup, and you can have your first query running in minutes.

If you’re job hunting in data engineering, Snowflake is the one showing up most in job listings right now. It’s worth learning regardless of which one your company uses.

If you’re at an AWS-heavy company, Redshift is likely already in your stack — and getting good at it will make you immediately useful to your team.

Which one does your company use? Drop a comment below 👇

Follow me on Instagram at https://www.instagram.com/techqueen.codes for visual SQL, Python and Snowflake tips every week 💙

What is Snowflake? A Beginner's Guide to the Cloud Data Warehouse Everyone's Talking About

Neha Christina — Wed, 11 Mar 2026 15:31:16 +0000

If you've seen "Snowflake" in job listings and had no idea what it meant — you're not alone.

It shows up everywhere in data engineering, data analytics, and even business intelligence roles. But nobody ever explains what it actually is in plain English.

Until now.

What is Snowflake?

Snowflake is a cloud-based data warehouse.

That's the technical answer. Here's the human one:

Think of a regular database like a filing cabinet in your office. It works fine when you have a few thousand files. But what happens when you have billions of files, hundreds of people searching at the same time, and the cabinet needs to grow overnight?

It breaks.

Snowflake is the solution. It's a giant, intelligent filing warehouse that lives in the cloud — it can grow instantly, never slows down under pressure, and lets hundreds of people query it simultaneously without fighting over resources.

How is it Different from a Regular Database?

Traditional databases like MySQL or PostgreSQL were designed for a different era. They work great for small-to-medium workloads, but they hit a wall when data gets big.

Here's what happens with traditional databases at scale:

They get slow. Query a table with billions of rows and you might be waiting minutes — or hours.

They're hard to scale. Need more capacity? You have to physically buy and set up new servers. That takes time and money.

They struggle with many users. The more people running queries at once, the slower it gets for everyone.

They're expensive to maintain. You need a dedicated database administrator just to keep things running smoothly.

Snowflake was built from the ground up to solve all four of these problems at once.

How Snowflake Solves It

Massively parallel processing. When you run a query, Snowflake doesn't run it on one machine. It splits the work across thousands of servers simultaneously. A query that would take 10 minutes on a traditional database can take seconds.

Auto-scaling. Need more compute power? Snowflake spins up additional capacity in seconds — automatically. When you're done, it scales back down. You only pay for what you use.

Multi-cluster warehouses. Snowflake can run multiple compute clusters at the same time. 100 analysts running queries simultaneously? Each gets their own resources. No one slows anyone else down.

Separation of storage and compute. This is the key architectural insight that makes everything else possible. In a traditional database, storage and compute are tightly coupled. In Snowflake, they're completely separate — which means you can scale each one independently.

5 Snowflake Terms You'll Hear Everywhere

Once you start working with Snowflake, these five terms come up constantly.

Virtual Warehouse — This is the compute engine. When you run a query, a virtual warehouse does the actual processing. You can have multiple virtual warehouses for different teams or workloads, and they don't interfere with each other.

Database & Schema — Just like in any SQL database, you organize your data into databases and schemas (think of them as folders and subfolders). Your tables live inside schemas.

Time Travel — If someone accidentally deletes a table or overwrites data, you can travel back in time and recover it — up to 90 days back on some plans. This has saved many data teams from disaster.

Zero-Copy Cloning — You can clone an entire database, schema, or table instantly with no additional storage cost. Snowflake doesn't duplicate the actual data; it just creates a pointer to the same underlying data. Perfect for creating test environments.

Snowpipe — Snowflake's continuous data ingestion service. Instead of loading data in batches, Snowpipe automatically loads new data into Snowflake as soon as it arrives in your cloud storage.

Should You Learn Snowflake?

Short answer: yes.

If you're working in data — or want to work in data — Snowflake is one of the most valuable skills you can add to your toolkit right now.

Here's why:

It's in thousands of job listings. Search for data engineer, data analyst, or analytics engineer on any job board and Snowflake shows up constantly.

It's growing fast. Snowflake is now used by over 8,000 companies including Netflix, Adobe, Capital One, DoorDash, and Pfizer.

Your SQL already works. If you know SELECT, WHERE, JOIN, and GROUP BY — you already know how to query Snowflake. The learning curve is much lower than people expect.

It commands higher salaries. Roles that list Snowflake as a requirement consistently pay more than equivalent roles that don't.

How to Get Started

The best way to start is free:

Go to app.snowflake.com and sign up for a 30-day free trial — no credit card required
Create a virtual warehouse (just click through the setup wizard)
Load a sample dataset (Snowflake provides several built-in ones)
Start querying with SQL you already know

Within an hour you'll have run your first Snowflake query and the whole thing will feel far less intimidating.

What's Next?

In a future post I'll cover how Snowflake compares to other cloud data warehouses like BigQuery and Redshift — and when to use each one.

For now: Snowflake is not as scary as it sounds, and if you already know SQL, you're more than halfway there.

Which Snowflake feature surprised you most? Drop a comment below 👇

Follow me on Instagram at https://www.instagram.com/techqueen.codes for visual SQL, Python and Snowflake tips every week 💙

SQL Window Functions Don't Have to Be Scary 🪟

Neha Christina — Mon, 09 Mar 2026 11:01:18 +0000

Introduction

If you've ever seen OVER (PARTITION BY ... ORDER BY ...) in a SQL query and quietly closed the tab — this post is for you.

Window functions are one of those topics that look intimidating at first but become incredibly powerful once they click. And once you learn them, you'll wonder how you ever wrote SQL without them.

By the end of this post you'll understand 5 essential window functions, when to use each one, and exactly how they differ from each other.

Let's go.

What Even Is a Window Function?

A regular aggregate function like SUM() or COUNT() collapses your rows into a single result. Window functions do calculations across rows — without collapsing them.

-- Regular aggregate: collapses all rows into one
SELECT dept, SUM(salary) FROM employees GROUP BY dept;

-- Window function: keeps all rows, adds a calculated column
SELECT name, dept, salary,
  SUM(salary) OVER (PARTITION BY dept) AS dept_total
FROM employees;

The key difference: GROUP BY gives you one row per group. Window functions give you all your rows back, plus a new calculated column alongside them.

The syntax always follows this pattern:

function_name() OVER (
  PARTITION BY column   -- optional: defines the group
  ORDER BY column       -- optional: defines row order within the group
)

Function #1: ROW_NUMBER()

Assigns a unique sequential number to each row within a partition. No ties — every row gets a different number.

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

Result:

name	dept	salary	row_num
Alice	Eng	90k	1
Bob	Eng	85k	2
Carol	HR	70k	1
Dave	HR	65k	2

Even if Alice and Bob had the same salary, they'd still get different row numbers.

When to use it:

Get the most recent record per user (filter where row_num = 1)
Deduplicate data
Paginate query results

Function #2: RANK()

Like ROW_NUMBER, but ties get the SAME rank — and the next rank skips.

SELECT
  name, score,
  RANK() OVER (
    ORDER BY score DESC
  ) AS rank
FROM scores;

Result:

name	score	rank
Alice	95	1
Bob	95	1
Carol	88	3
Dave	75	4

Notice rank 2 is missing. Alice and Bob both scored 95 so they share rank 1 — and the next rank jumps to 3.

When to use it:

Ranking students by exam score
Ranking products by sales volume
Any scenario where ties are meaningful and gaps are acceptable

Function #3: DENSE_RANK()

Like RANK, but NO gaps after ties.

SELECT
  name, score,
  DENSE_RANK() OVER (
    ORDER BY score DESC
  ) AS dense_rank
FROM scores;

Result:

name	score	dense_rank
Alice	95	1
Bob	95	1
Carol	88	2
Dave	75	3

Same data — but now Carol gets rank 2, not rank 3.

RANK vs DENSE_RANK — the simple rule:

Use RANK() when gaps make sense (sports — if two people tie for gold, no one gets silver)
Use DENSE_RANK() when gaps look wrong (medal tables, internal employee levels)

Function #4: LAG()

Lets you look at the value from the PREVIOUS row.

SELECT
  month, revenue,
  LAG(revenue, 1) OVER (
    ORDER BY month
  ) AS prev_revenue
FROM sales;

Result:

month	revenue	prev_revenue
Jan	10k	NULL
Feb	12k	10k
Mar	11k	12k
Apr	15k	11k

January has NULL for prev_revenue because there's no row before it.

When to use it:

Month-over-month or year-over-year comparisons
Calculating the change between consecutive rows
Detecting gaps or jumps in a time series

Function #5: LEAD()

Like LAG — but looks at the NEXT row instead.

SELECT
  month, revenue,
  LEAD(revenue, 1) OVER (
    ORDER BY month
  ) AS next_revenue
FROM sales;

Result:

month	revenue	next_revenue
Jan	10k	12k
Feb	12k	11k
Mar	11k	15k
Apr	15k	NULL

April has NULL because there's no row after it.

The Cheat Sheet

Function	Ties	Gaps	Best For
`ROW_NUMBER()`	Different numbers	N/A	Deduplication, pagination, latest record per group
`RANK()`	Same rank	Yes	Sports rankings, exams
`DENSE_RANK()`	Same rank	No	Leaderboards, levels, medal tables
`LAG()`	N/A	N/A	Compare to previous row
`LEAD()`	N/A	N/A	Compare to next row

One Thing to Remember

All window functions follow the same structure:

function() OVER (
  PARTITION BY ...   -- your "group by" (optional)
  ORDER BY ...       -- row order within the group (optional)
)

PARTITION BY is like GROUP BY — but it doesn't collapse your rows. ORDER BY inside OVER() controls how rows are ordered within each window, independently of the query's own ORDER BY.

What's Next?

Once you're comfortable with these 5, explore:

SUM(), AVG(), COUNT() as window functions
NTILE() for splitting rows into buckets
Frame clauses like ROWS BETWEEN for rolling calculations

Which window function confused you the most before reading this? Drop a comment below 👇

Follow me on Instagram at https://www.instagram.com/techqueen.codes for visual SQL, Python and Snowflake tips every week 💙

🐍 5 Python Mistakes Every Junior Dev Makes (And How to Fix Them)

Neha Christina — Fri, 06 Mar 2026 08:19:16 +0000

Introduction

If you're a junior developer writing Python, chances are you've made at least one of these mistakes — and you didn't even know it. These aren't syntax errors that your IDE catches. They're subtle bugs and bad habits that slip through code reviews and slow down your growth.
Let's fix all five.

Mistake #1: Mutable Default Arguments

The problem:

def add_item(item, lst=[]):
    lst.append(item)
    return lst

This looks harmless, but Python creates the default list once at function definition — not on every call. So every call shares the same list.

The fix:

def add_item(item, lst=None):
    if lst is None:
        lst = []
    lst.append(item)
    return lst

Always use None as your default for mutable arguments, then create the object inside the function.

Mistake #2: Skipping List Comprehensions

The problem:

squares = []
for i in range(10):
    squares.append(i ** 2)

The fix:

squares = [i ** 2 for i in range(10)]

Cleaner, faster, and more Pythonic. Senior devs will notice immediately.

Mistake #3: Bare Exception Catching

The problem:

try:
    result = 10 / 0
except:
    pass

A bare except catches everything — including KeyboardInterrupt and SystemExit. This makes bugs invisible and debugging a nightmare.

The fix:

try:
    result = 10 / 0
except ZeroDivisionError as e:
    print(f"Error: {e}")

Always catch specific exceptions.

Mistake #4: String Concatenation in Loops

The problem:

result = ""
for word in words:
    result += word

Each += creates a brand new string object in memory. In a loop, that's O(n²).

The fix:

result = "".join(words)

.join() is O(n) and the idiomatic Python way to build strings from iterables.

Mistake #5: Not Using enumerate()

The problem:

for i in range(len(fruits)):
    print(i, fruits[I])

The fix:

for i, fruit in enumerate(fruits):
    print(i, fruit)

enumerate() exists exactly for this. Use it — it's cleaner and avoids off-by-one errors.

Which one were you guilty of? Drop a comment below 👇
Follow me on Instagram https://instagram.com/techqueen.codes for visual SQL, Python & Snowflake tips every week.

Enjoyed this? Save the visual cheat sheet on Instagram → https://instagram.com/techqueen.codes