If you’re measuring your SQL progress, there’s a single gate that separates analysts who can handle a real job from analysts who stall: window functions. Before window functions, you’re writing aggregates and self-joins; after, you’re writing queries that would have taken a junior two stabs and a coffee break in 15 lines of readable SQL. This post covers the three window-function patterns that do 90% of real analyst work, by use case — running totals, top-N-per-group, and LAG/LEAD for growth math.

Why window functions are the career milestone

The question every window function answers is: _for each row, what’s the value of some expression over a related group of rows?_ "Related" is flexible — it might be "all rows for the same customer," or "all rows before this one chronologically," or "all rows in the same month." Once you have that frame, you can attack every real-world analytics problem that SQL can solve.

Before window functions, analysts solved these problems with self-joins, correlated subqueries, or by pulling data into Python for post-processing. All three work; all three are either slow, ugly, or both. Window functions replace all of them with a syntax that runs on the database engine, scales to any row count the engine can handle, and reads in order from top to bottom the way your brain wants to read SQL.

A window function has three pieces: the function itself (SUM, COUNT, ROW_NUMBER, LAG, etc.), a PARTITION BY (which rows to group together), and an ORDER BY (how to sequence rows within the group). Master those three pieces and you’ve mastered the feature.

The vocabulary

Everybody learning window functions eventually has the same "oh" moment. It goes like this: `PARTITION BY` is just `GROUP BY` that doesn’t collapse the rows. In a `GROUP BY` query, you get one row per group. In a `PARTITION BY` query, you get all the original rows back, each annotated with its group’s aggregate.

That’s the whole mental model. Once it clicks, every window function makes sense. Want a running total of revenue by customer? Partition by customer, order by date, sum the amount. Want each customer’s total revenue duplicated onto every one of their purchase rows? Partition by customer, no order by, sum the amount. Want the largest single purchase for each customer stamped onto every row? Same pattern with `MAX`.

The `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` is the default when you include `ORDER BY` in a window with a cumulative aggregate, but making it explicit is worth it — partly for readability, partly because the default is different across dialects and you’ll forget which is which.

The second canonical window-function pattern is "top N per group." Top 3 campaigns by revenue in each quarter. Top 5 customers by spend in each region. Most recent purchase per customer. All of them are some flavor of: partition, order, pick the top rows.

The three ranking functions differ only in how they handle ties:

99% of the time, you want `ROW_NUMBER`. The only common case for `RANK` is when you’re asked for "the top 3" and want to include all ties at rank 3. `DENSE_RANK` mostly shows up in sports-ranking problems and very rarely in real analyst work.

The CTE-then-filter pattern is worth burning into your fingers. You’ll use it for every top-N-per-group query, first-per-customer query, and most-recent-per-entity query for the rest of your career. Note that the WHERE clause filters on the computed `rn` column — SQL doesn’t allow window functions inside WHERE directly, which is why the CTE is necessary rather than optional.

The single most common real-world window-function query is "give me the most recent X per entity." It’s always ROW_NUMBER, partition by the entity, order by timestamp DESC, filter rn = 1. Memorize that skeleton — most-recent-login-per-user, last-purchase-per-customer, current-price-per-product all fit it.

The "first per group" shortcut

The third pattern is the one that quietly earns analysts their seniors stripes: any time you need to compare a row to the row before or after it, you want `LAG` or `LEAD`. Month-over-month growth. Week-over-week change. Time between consecutive purchases. Session-to-next-session gap.

Both functions take an offset (default 1) and optionally a default value for when there is no previous row (usually NULL). `LAG(x)` is "the value of x in the previous row." `LEAD(x)` is "the value of x in the next row." With a `PARTITION BY`, "previous" and "next" are scoped to the group. With an `ORDER BY`, they’re scoped to the sort order.

Month-over-month revenue is the canonical `LAG` query and one you’ll write dozens of times. The skeleton never changes:

WITH monthly AS (
  SELECT STRFTIME('%Y-%m', date_col) AS month,
         SUM(metric) AS total
  FROM source_table
  GROUP BY month
)
SELECT month,
       total,
       LAG(total) OVER (ORDER BY month) AS prev,
       total - LAG(total) OVER (ORDER BY month) AS delta,
       ROUND((total - LAG(total) OVER (ORDER BY month)) * 100.0
             / NULLIF(LAG(total) OVER (ORDER BY month), 0), 1) AS pct
FROM monthly
ORDER BY month;

Swap the metric and the source table and this is half of every business-review slide deck ever built.

Watch what happens with `PARTITION BY campaign_id`: January-to-February comparisons stay within the same campaign, not across campaigns. If you forgot the partition, you’d get month-over-month growth comparing Campaign A’s February to Campaign B’s January, which is nonsense. This is the single most common window-function bug — missing partition means bleeding rows across the group boundary you intended.

Window functions are the moment SQL stops feeling like a query language and starts feeling like a modeling language. Once you reach for them by reflex, the queries you write stop needing post-processing in Python or Excel for 90% of analyst work. That’s the career milestone — not knowing the syntax, but knowing which pattern to reach for.

If you want to build window-function reflexes against a realistic dataset with marketing-style time-series quirks (uneven reporting gaps, mid-month backfills, campaigns that end and restart), the Business Intelligence path on caseSQL is designed around exactly these patterns.

Four gotchas that bite every analyst who’s new to windows, in rough order of how often they show up in real queries:

The frame-clause one is the most subtle and the hardest to debug — numbers come out looking _almost_ right, and only eagle-eyed readers catch that two adjacent rows with the same date share a running total. Once you’ve been burned once, you write `ROWS BETWEEN` explicitly for every cumulative window for the rest of your career.

PostgreSQL, Snowflake, BigQuery, Redshift, MySQL 8+, and SQLite 3.25+ all support the core window-function set (ROW_NUMBER, RANK, LAG, LEAD, SUM/AVG/COUNT with OVER). Snowflake and BigQuery add `QUALIFY` for filtering on window results without a CTE. BigQuery adds `NAVIGATION` functions for richer temporal logic. For 95% of analyst work, stick to the core set — it ports anywhere.

The dialect cheat sheet

Window functions are the career-milestone SQL skill. Running totals, top-N-per-group, and LAG/LEAD for month-over-month growth, explained by use case with exercises.

SQL Window Functions in Real Analyst Work | caseSQL

SQL Window Functions in Real Analyst Work

Senior analysts don’t have a magical SQL skill juniors lack. They have a specific workflow they run before they trust a single number the database returns. The workflow comes from being burned — every senior analyst can tell you about the quarterly review where their number was off by 30% because of a silent join. This post is that workflow, distilled. Five steps, in order, with exercises.

Databases are messy because they’re alive

A database is not a dataset. A dataset is a snapshot: frozen in time, reasoned about as a static artifact, teachable by a course. A database is a living system: being written to by ten different services, backfilled by Tuesday’s cron, shaped by three product managers’ choices over five years, with a schema that still carries scars from the 2021 migration that went halfway.

When you query a database, you’re asking a question of a system that has layers of sediment: the original schema, the first emergency hotfix, the second schema migration that nobody named, the columns added for a feature that shipped and was deprecated, the NULLs that mean "not recorded" in one table and "not applicable" in another. Every one of those layers is a potential lie.

Senior analysts know this in their bones. They treat every new table as hostile until proven otherwise. That’s not paranoia — it’s correctness.

Tables lie by omission (NULL instead of missing), by duplication (same entity counted twice with different IDs), and by drift (column semantics changed on a deploy you weren’t told about). All three are invisible to the query engine. Only the analyst can catch them.

The three lies a database tells

Start with the data dictionary (or the lack of one)

The first thing a senior analyst does in a new database is not write a query. It’s look for the data dictionary — the document that says what each table and column means, who owns it, and what the known gotchas are.

About 30% of companies have a real, maintained data dictionary. Another 30% have a stale one in a wiki page that was last updated in 2022. The remaining 40% have nothing — you ask the person who built the schema, if they still work there, or you reverse-engineer it from dbt models, or you piece it together from SQL written by the analyst who was there before you.

Either way, step 1 is to write your own. It doesn’t have to be elaborate. For every table you’re going to query, spend five minutes writing: what does each column mean in one sentence, what’s the grain (one row per what?), and what joins are expected. Do this in a `schema.md` file in your work repo. Six months from now, future-you will thank present-you.

This takes five minutes and surfaces three things almost every time: a table that’s much bigger than you expected (hint: probably double-writes), a time range that doesn’t go back as far as you thought (hint: the backfill stopped in 2023), and a table you didn’t know existed (hint: it’s the one you actually need).

Profile: NULLs, duplicates, inconsistencies

Once you know what tables exist, profile the ones you’ll actually query. Profiling is the five-minute cousin of a full data-quality audit. You’re not trying to be thorough — you’re trying to surface the obvious lies before they bite you.

Notice the `LOWER(TRIM(...))` in the duplicate check. Email case-sensitivity and leading-whitespace bugs are the two ways duplicate emails most commonly slip past the uniqueness check. Never trust `COUNT(DISTINCT email)` on a raw column; always normalize first.

Every team has folklore — numbers that circulate in slide decks and all-hands meetings, numbers everyone "just knows," numbers that were true in 2023 and have been repeated ever since. Part of the senior-analyst job is reconciling what the data says with what the folklore says.

The reconciliation itself is the skill. The folklore usually has a kernel of truth, which means the answer isn’t "the data is right, the folklore is wrong." It’s "the folklore was right _under these assumptions_; under current assumptions, the number is different; here’s why." That’s a useful answer. "Your number is wrong" is not a useful answer.

Concrete move: when a stakeholder cites a number you can’t reproduce from the database, your first question is "what definition was this using?" You’ll usually find that the 2023 version filtered differently, used a different time window, or excluded a segment the current number includes. Half the value of a senior analyst is being the person who writes the new definition down so nobody has to re-litigate it.

If your org has a dashboard everyone trusts, and you’re about to report a number that contradicts it, stop. Don’t publish the new number. First reproduce the dashboard number from the database, figure out why it’s different from your new query, and write up the delta. Reporting a conflicting number without explanation is the fastest way to lose stakeholder trust, even if your number is actually more correct.

The dashboard-of-record problem

A working mental model: assume the data lies

Tie it together with one mental model you can drill into reflex: **assume the data lies until proven honest.** Every query is innocent until guilty-until-proven-innocent.

In practice this means: before you hand a number to anyone, you’ve done three things. You’ve checked the scale (does the order of magnitude match a rough back-of-envelope?). You’ve checked the delta (is the change from last period believable without an external event?). You’ve checked the top row (does the biggest contributor make sense?). All three take 90 seconds. All three catch the vast majority of query bugs.

The first time you use this pass, it catches a bug. You find a join fanning out, or a filter that was tighter than you thought, or a campaign name that broke out of its category. After the hundredth time, it’s muscle memory: every headline number gets the three-check pass before it leaves your laptop. That’s the workflow. That’s what "senior analyst" looks like when you zoom all the way in.

After a year or two on any production database, you’ll develop a mental taxonomy of the bugs that come up again and again. The categories differ by org, but the shape is always similar:

Every one of these has the same fix — you catch it by running the sanity pass, then you write a CTE or a filter that documents the guard explicitly at the top of your query, with a comment explaining why. Six months later, when another analyst copies your query, the guard carries forward. That’s how institutional knowledge gets encoded in a database team.

Real databases are inconsistent, half-documented, and full of edge cases. A senior-analyst workflow for profiling, sanity-checking, and trusting-but-verifying before you report numbers.

How to Read a Messy Database Like a Senior Analyst | caseSQL

How to Read a Messy Database Like a Senior Analyst

If you’re reading this as a nurse, a teacher, a bartender, an ops coordinator, an accountant, a bench scientist, or anything else that isn’t "software engineer": good news — the data analyst path is open to you, and has been for years. The credential gatekeeping you may have been warned about applies almost entirely to software engineering, not analytics. What actually matters for analytics hiring is a mix of SQL literacy, judgment under ambiguity, and the ability to explain a number to a non-technical stakeholder. None of those require a CS degree. Two of them are _easier_ to acquire if you already have another career under your belt.

You don’t need a CS degree to write SQL

SQL is declarative. You describe the result you want; the engine figures out how to get it. That sentence is the reason the language is learnable in weeks rather than years, and the reason analytics hiring doesn’t filter on the same signals software engineering does.

Compare that to a typical software role, where you’re expected to reason about memory, concurrency, testing frameworks, deployment pipelines, and a stack-specific language idiom. That stack is what the CS degree quietly teaches, and why bootcamp-to-SWE transitions take so long. None of it is in the analyst job description.

What’s actually in the analyst job description: write a query, understand the business context of the result, communicate the result to someone who doesn’t know SQL. If you came from a role that involved explaining complicated things to stakeholders — healthcare, education, operations, client services — you already have half the job. You just need the query-writing half.

I’ve been on the interview panel for analyst roles at three companies. The signal hiring managers weight most heavily isn’t clever SQL. It’s _trustworthy SQL_. Two questions run in the background of every interview:

That’s it. Everything else — window functions, dialect trivia, query optimization — is a distant second. Which means your job as a career changer is to build evidence of _judgment_, not syntax mastery. A portfolio that shows "I can write a medium-complexity query, and here are the assumptions I wrote down next to it" beats a portfolio of 50 LeetCode-style SQL puzzles, every time.

Ex-teachers and ex-clinicians do disproportionately well in analyst hiring once they have the SQL chops. The reason: both professions involve explaining something technical to a non-technical audience under time pressure. That muscle is exactly what "present this number to the VP of marketing" needs.

The underrated signal

Full disclosure: 90 days only works if you can sustain 8–10 hours per week of focused practice. If you can do more, it accelerates. If you can do less, just stretch the plan — the sequence matters more than the calendar.

**Weeks 1–3: Core SQL syntax.** SELECT, WHERE, GROUP BY, the six join types, subqueries, CTEs. Use one free resource all the way through rather than hopping — consistency compounds. By the end of week 3, you should be able to write a query that joins three tables, aggregates a numeric column, and filters the result, without looking things up.

**Weeks 4–6: Window functions and dirty data.** ROW_NUMBER, RANK, LAG, LEAD, running totals. Simultaneously, start practicing against a dataset that has real-world problems — NULLs, duplicates, bad dates. The caseSQL starter path is designed for exactly this window.

**Weeks 7–9: Open-ended problems.** Stop doing puzzle-style exercises. Start picking questions like "how is the business doing" and forcing yourself to write a full analysis — assumptions, query, result, one paragraph of commentary. Quality over quantity: one full analysis a day beats five puzzle solutions.

**Weeks 10–12: Portfolio and interview prep.** Pick 3 of your analyses and polish them into shareable form. Set up a GitHub repo, write a short README for each, put them in order. In parallel, start doing mock interviews — ideally with a working analyst friend, but even narrating your thought process out loud into a voice memo helps.

The single biggest unlock for career changers is realizing that a portfolio doesn’t require a data job to build. You need three things: a public dataset, a notebook or markdown file that shows your work, and a GitHub repo that hosts it. That’s it.

Good public datasets for analyst portfolios: NYC TLC trip data (taxis), the Citibike dataset, the Nashville housing dataset, the Brazilian Olist e-commerce dataset, any of the Kaggle marketing or retail datasets. Pick one. Spend two weeks on it. Produce a portfolio piece.

What makes a portfolio piece senior-looking, not junior-looking:

That structure is the best signal you can send that you think like an analyst, not like a student. Three of those in a public repo outperforms twenty LeetCode solutions in every screening conversation I’ve sat in.

Most analyst interviews have four stages: a phone screen (resume-walk + why-analytics), a SQL screen (live coding, 45–60 min), a case study (open-ended business question, take-home or 90-min on-site), and a behavioral round (collaboration, communication, handling ambiguity).

The SQL screen is the one people stress about, but it’s rarely the stage that kills candidates. The case study is. Case studies are where tutorial-SQL trained candidates fall apart — they can write the queries but can’t handle the ambiguity of the prompt.

Practice the case study explicitly. Give yourself a business prompt (search "data analyst case study interview" for sample datasets and prompts), set a 90-minute timer, and produce a deliverable: 1 summary slide, 2–3 supporting charts, 4–5 bullet points of insight. Do three of these before your first real interview and the format will stop feeling foreign.

The interview signal those three queries send together: you think about _arguments_ not just queries, you understand trend + efficiency + mix as the three legs of a growth story, and you know when to compare windows rather than just aggregating one. That’s the career-changer advantage — you already know how to tell a story to a non-technical audience, you’re just learning a new medium.

Things career changers worry about that don’t matter

I’ve coached a few dozen career changers through analytics job searches, and the same three non-problems eat up 80% of their anxiety. None of them are worth the time:

What matters instead: you can write a query against a real schema without peeking at a tutorial, you can talk about a number without jargon, and you have one polished portfolio piece that demonstrates both. That’s the bar.

Realistic timelines for a career change into analytics, assuming no prior data experience: 3–6 months of focused part-time practice to be interview-ready, 2–4 months of active job search after that. Full-time study compresses the first number; a lucky network warm intro compresses the second. Don’t quit your day job on week one.

The numbers on timing

You don’t need a computer science degree to become a data analyst. A concrete 90-day plan, portfolio strategy, and interview breakdown for career changers.

SQL for Career Changers: No CS Degree Required | caseSQL

SQL for Career Changers: No CS Degree Required

There’s a specific kind of person who aces every SQL course, passes every LeetCode-style question bank, and then freezes on their first real analytics ticket. It’s not a confidence problem and it’s not a skill gap in the normal sense. It’s a category error: the thing they practiced for is a _different job_ from the one they got hired into.

This post is about that category error. What tutorial SQL actually measures, what job SQL actually requires, and how to close the gap on purpose — not by grinding more questions, but by changing what you practice.

The modal SQL course gives you a clean schema, a closed question ("find the 3rd-highest salary"), and a single correct answer that a grader can verify. You learn syntax, you learn patterns, you get faster. That’s real progress — but it’s progress against a narrow target.

Your first real ticket looks like this: **"Hey, can you pull what happened with last week’s campaign? Something seems off."** No schema attached. No clear definition of "what happened." No specified columns, no specified result shape, no verification oracle. And the data you’re querying has two customers with the same email, one campaign that was renamed mid-flight, and a backfill job that double-counted Tuesday.

The SQL skill that actually matters in this situation — translating an ambiguous human question into a concrete query, then stress-testing the answer before you hand it over — was never trained by the course. It can’t be, because auto-graders can’t evaluate judgment.

Tutorial SQL measures: can you write a query that returns the answer. Job SQL measures: can you figure out what the question actually is, write a query that returns a defensible answer, and flag the three reasons the answer might be wrong. The second skill is what managers pay senior salaries for.

The reframe

Tutorial data is clean. Real data isn’t.

Go look at any beginner SQL exercise. The `customers` table has an `email` column with no nulls, no duplicates, no weird casing, and every row has exactly one corresponding row in `orders`. That dataset doesn’t exist in nature. What exists in nature looks like this:

A learner who only practiced against clean data will write a query that looks correct and returns a number that is _wrong_ — not off by a little, sometimes off by 3x. They’ll hand that number to a stakeholder who will act on it. The mistake won’t surface for weeks, and when it does, it will surface as "the new analyst isn’t reliable."

Notice what’s happening here — the actual SQL is not harder than what a tutorial teaches. `ROW_NUMBER()`, `PARTITION BY`, a `CASE` inside an `ORDER BY`. What’s different is _the awareness that dedup was required in the first place_. That awareness only comes from seeing a dataset where the bug exists and burning yourself on it once.

Tutorials ask closed questions. Jobs ask open ones.

A closed question has a verifiable answer. "Find the 3rd-highest salary" is closed. "How are we doing on mobile this quarter" is open. Analysts spend roughly zero time on closed questions. Every real ticket arrives open, and the first 30 minutes of the work is _closing it_ — figuring out what the stakeholder actually wants.

Closing a question is its own skill. It means:

The senior analyst move is to write out those assumptions at the top of the query, ship the query _with the assumptions visible_, and let the stakeholder correct you. The junior move is to guess silently and be wrong.

The query itself is week-two SQL. What makes it a senior’s answer is the block above it. Stakeholders almost never correct all of the assumptions, but they usually correct one — and catching that one before you shipped the wrong number is the entire value of the assumptions block.

The gap between tutorial SQL and job SQL isn’t closed by more syntax practice. It’s closed by changing the _shape_ of what you practice. Three concrete moves:

This is the gap caseSQL was built to close. Every mission comes from a fictional manager with an open question, runs against a schema with planted data-quality issues, and is graded by a 5-tier validator that can’t just check a hash — it has to evaluate whether your result is _defensible_. That’s the feedback loop tutorial SQL can’t give you, and it’s what separates people who _know_ SQL from people who _use_ SQL for a living.

If you’re 3–6 months into SQL practice and starting to worry you’re "not progressing," the fix is almost never more tutorials. It’s changing the shape of the problem. Do this for 30 days:

Most people who "hit a wall" at month 3 are finishing week 12 of tutorial grinding and haven’t done week 1 of the reset. The wall isn’t a skill ceiling — it’s the syllabus ceiling. Change the syllabus and the wall disappears.

The single highest-leverage practice habit is writing the assumptions block above your query, every time, for every query, even when you think the question is obvious. Six weeks of that habit will put you ahead of most analysts with two years of experience on the dimension that hiring managers actually weight.

If nothing else, take this

Most SQL courses teach syntax against clean data and closed questions. Real analyst jobs ask open questions against dirty data. This post is the bridge, with exercises.

Why SQL Learners Fail at Real Jobs (And How to Fix It) | caseSQL

Why SQL Learners Fail at Real Jobs (And How to Fix It)

Marketing analytics lives in a narrow band: big enough that spreadsheets buckle, small enough that the data team doesn’t prioritize it. Which means the person closest to the numbers — you — needs to pull them yourself. SQL is how. This guide is the shape of the role distilled to its query-writing parts: the five reports you’ll run every week, the way attribution actually gets computed, and the data-quality gotchas that make senior analysts cautious and junior analysts confident.

Everything below assumes a star schema that looks like most modern marketing warehouses: `dim_customers`, `dim_campaigns`, `fact_sends`, `fact_sessions`, `fact_purchases`. If you’ve worked with a dbt project, a Snowflake data model, or the caseSQL mission database, the shape will be familiar.

Excel is an excellent tool up to about 100,000 rows and one dimension of analysis. Past that, three things break at once: pivot performance, referential integrity (lookups silently fail when the sheet exceeds a cache boundary), and shareability — the file becomes the single source of truth for something it was never designed to own.

SQL solves each of those, but the real reason to learn it is different: **SQL lets you ask the next question.** In Excel, the fastest path is to pick a pivot, format it, and move on. In SQL, you can always go one layer deeper — group by a new dimension, join a new table, filter by a cohort — in the same window. That’s what senior analysts do all day, and it’s the reason recruiters screen for SQL before Tableau: the query language is the skill; the dashboard tool is the output.

Most marketing analytics teams run on PostgreSQL, Snowflake, BigQuery, or Redshift. For 90% of analyst work, the syntax is identical. The differences matter when you hit window functions (supported everywhere), date arithmetic (each vendor has its own functions), and arrays (a BigQuery special). This guide uses ANSI SQL; the exercises run against SQLite, which is the simplest dialect and forces good habits.

The SQL dialect question

The five queries every marketing analyst writes weekly

Every marketing analytics job has five query templates that come up over and over. Mastering the templates (not memorizing exact SQL) is 80% of what makes the role feel mechanical after the first three months.

The first one is the foundation. If you can write it cleanly, you can adapt it to the other four by swapping the `GROUP BY` column, adding a time dimension, or filtering on a segment.

Two subtle details to notice. First, `COUNT(DISTINCT customer_id)` not `COUNT(*)` — sends and purchases both have duplicate-row risk if a customer was hit twice. Second, `NULLIF(..., 0)` on the denominator so a zero-send campaign doesn’t crash the query; it just returns null. Those two habits alone mark the gap between a mid-level and a senior analyst.

Attribution in SQL: last-touch, first-touch, and reality

Attribution is where marketing analytics stops being arithmetic and starts being policy. The question "which campaign got credit for this purchase" has no mathematically correct answer — only a convention your org agrees on. The three conventions you’ll implement: **last-touch** (credit the last campaign before purchase), **first-touch** (credit the first campaign the customer ever saw), and **linear** (split credit evenly across all touches).

In SQL, all three are window-function problems. For last-touch, you want the most recent `fact_sends` row before each `fact_purchases` row. `ROW_NUMBER()` partitioned by customer, ordered by send date descending, filtered to rows before the purchase, take the first one. Once you’ve done that pattern three times, it becomes muscle memory.

First-touch is the same query with `ORDER BY sent_at ASC`. Linear attribution is trickier: you count how many touches a purchase had, then divide the purchase amount by that count. If your org ever asks for time-decay or U-shaped attribution, you’re implementing a weighted version of linear — same SQL skeleton, different weights.

Here’s what tutorials don’t tell you: most of the time a marketing query looks wrong, the query is right and the data is wrong. Refunded purchases left in the fact table. Campaign IDs that don’t match `dim_campaigns` because the campaign was renamed. Customers who show up twice because the CRM merged an old record. Timezone drift so a "last 30 days" window is actually 29.5.

Senior analysts assume the data lies until proven honest. Before they report a number upward, they run three checks:

If your `fact_purchases` table has a `refunded_at` column, every revenue query needs to decide: include refunded purchases at face value (overstates revenue), exclude them entirely (understates top-of-funnel signal), or subtract refund amount from purchase amount (cleanest). Pick one convention, put it in a comment at the top of the query, and apply it consistently.

Refunds are a fact table, not a filter

From queries to insights: the analyst-sense layer

The skill that separates a junior marketing analyst from a senior one isn’t writing more complex SQL. It’s noticing which number in a report matters and which is noise. That judgment comes from pattern recognition, which comes from seeing the same query under lots of different conditions — normal weeks, holiday spikes, bug weeks, refund waves, data-outage Wednesdays.

The fastest way to build that pattern-recognition is to write one query, then run 10 variations of it. What if I segment by new vs returning? What if I split paid vs organic? What if I look at the last 7 days instead of 30? Each variation teaches you what the numbers normally look like, so when they don’t, you notice.

When you run this against a real dataset, two things happen. First, you start to develop a feel for what a "normal" new-customer share looks like for your org (usually 10–30%). Second, when a campaign comes in at 70%, you know immediately whether it’s a genuinely great acquisition campaign or whether the campaign ID got re-used and the "customers" are really old ones mislabeled. That reflex — _does this number behave?_ — is what your manager is paying for.

If you want to build that reflex against a realistic marketing star schema with planted data-quality issues, the Marketing Analytics path on caseSQL is the fastest way. 25 missions, each one a real analyst ask, validated against multiple accepted approaches so you see the trade-offs rather than a single "right answer."

Before any marketing query leaves your laptop, run this three-line mental pass. It takes 20 seconds and catches 80% of the bugs that make junior analysts look unreliable:

None of these three are advanced SQL. All three are the difference between a query that looks right and a query that _is_ right.

A practical guide to the SQL skills marketing analysts actually use on the job — attribution, campaign ROI, segment analysis, and handling messy data. Free exercises included.

Real SQL for real analyst work

SQL Window Functions in Real Analyst Work

How to Read a Messy Database Like a Senior Analyst

SQL for Career Changers: No CS Degree Required

Why SQL Learners Fail at Real Jobs (And How to Fix It)

SQL for Marketing Analysts: The Complete Guide

Ready to write real analyst SQL?