What SQL Do Data Analysts Actually Use? (And How Much Is Enough to Get Hired)

What a Data Analyst's SQL Day Looks Like

Analyst SQL work falls into a handful of recurring jobs. Here they are, roughly from most to least common.

1. Answering ad hoc questions

Someone from marketing, product or finance asks: "How many people signed up last month, and how does that compare to the month before?" You write a query, sanity-check the result, and send back a number or a small table. This is the bulk of the work for most junior analysts.

SELECT DATE_TRUNC('month', created_at) AS month,
       COUNT(*) AS signups
FROM users
WHERE created_at >= DATE '2026-01-01'
GROUP BY 1
ORDER BY 1;

Nothing advanced: filtering, grouping by a date, counting. What makes it hard in practice is knowing which table is the source of truth, whether test accounts should be excluded, and what "signed up" actually means at your company.

2. Building and maintaining recurring reports

Weekly revenue, monthly active users, conversion by channel. These start as ad hoc queries, then get saved as views or loaded into a dashboard tool like Tableau, Power BI or Looker. You'll spend real time maintaining these: fixing them when a source table changes, adding a new breakdown, or explaining why this week's number looks odd.

3. Combining data from several tables

The answer to almost any interesting question lives in more than one table: orders joined to customers, joined to marketing campaigns, joined to refunds. JOINs, and the row-multiplication mistakes that come with them, are your daily bread.

SELECT c.acquisition_channel,
       COUNT(DISTINCT c.id)       AS customers,
       SUM(o.amount)              AS revenue,
       SUM(o.amount) / COUNT(DISTINCT c.id) AS revenue_per_customer
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.created_at >= DATE '2026-01-01'
GROUP BY c.acquisition_channel;

4. Categorizing and cleaning

Raw data is messy. You'll write a lot of CASE WHEN to bucket values, COALESCE to handle missing ones, and string and date functions to tidy things up.

SELECT CASE
         WHEN amount < 50  THEN 'small'
         WHEN amount < 500 THEN 'medium'
         ELSE 'large'
       END AS order_size,
       COUNT(*) AS orders
FROM orders
GROUP BY 1;

5. Trend and behavior analysis

Retention, funnels, "what did users do before they churned?", month-over-month growth. This is where CTEs and window functions earn their place: ranking, running totals, comparing each period to the previous one with LAG.

6. Checking data quality

Duplicate rows, orders with no customer, dates in the future, totals that don't match the finance system. Good analysts write quick validation queries before trusting any number they send out.

-- Are there duplicate orders?
SELECT order_id, COUNT(*)
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

Concepts You'll Use Constantly

If you're job hunting, these are the non-negotiables. You should be able to write them without looking anything up:

• SELECT, WHERE, ORDER BY, LIMIT, including IN, BETWEEN, LIKE and IS NULL
• INNER and LEFT JOIN, and knowing which one a question needs
• GROUP BY with COUNT, SUM, AVG, MIN, MAX, plus COUNT(DISTINCT ...)
• HAVING, and why it's different from WHERE
• CASE WHEN for bucketing and conditional aggregation
• Date functions: truncating to week or month, date differences, filtering date ranges
• NULL handling: COALESCE, and how NULLs affect counts and filters
• CTEs (WITH) to break a problem into readable steps
• Window functions: ROW_NUMBER, RANK, running SUM, LAG/LEAD

That list covers the large majority of real analyst queries and most interview questions.

Concepts You'll Rarely Touch as an Analyst

Courses often teach these alongside the essentials, which makes beginners think they're equally important. For analyst roles, they usually aren't:

• Stored procedures, triggers and cursors: more common in database developer and backend roles
• Database administration: user permissions, backups, server configuration
• Writing complex schema designs from scratch: you'll mostly query tables that already exist (though understanding primary and foreign keys is essential)
• Recursive CTEs: useful occasionally for hierarchies, but rare day to day

They're worth knowing exist. They're not worth delaying your job applications over.

In analyst work, the advanced skill isn't fancy syntax. It's noticing when a JOIN doubled your revenue, or when "active user" means three different things in three different tables.

How Much SQL Is "Enough" at Each Level?

Entry level

Comfortable with everything in the "constantly" list, with window functions at a basic level. Can take a plain-English question and produce a correct query, even if it's not the most elegant one. Checks results before sending them.

Two to four years in

The syntax is the same; the judgment is different. You're expected to:

• Write longer, multi-step queries (several CTEs) that other people can read and maintain
• Know the company's data well enough to spot when a number is off
• Handle retention, cohort and funnel analysis without a template
• Have a basic sense of performance: filter early, avoid unnecessary SELECT * on huge tables, read an EXPLAIN plan when something is slow
• Push back on a vague request and turn it into a precise definition

A Ready-to-Apply Checklist

You're in good shape to start applying when you can do each of these without help:

1. Find customers who have never placed an order (LEFT JOIN plus IS NULL, or NOT EXISTS)
2. Calculate monthly revenue and month-over-month growth (GROUP BY a month, then LAG)
3. Find each customer's most recent order (ROW_NUMBER in a CTE)
4. Count orders per customer, including customers with zero orders
5. Bucket values with CASE WHEN and count each bucket
6. Find and remove duplicate rows
7. Explain in plain English why your JOIN returned more rows than you expected

Number 7 matters more than people think. Interviewers often care less about whether you write perfect syntax and more about whether you can reason about what your query is doing.

Practice on Questions That Feel Real

The fastest way to build these skills is to answer realistic questions against a real database, where you have to figure out which tables matter and check your own results. That's how the cases in Murder in Blackwater Bay and Story Mode work: every question is a small investigation, not a fill-in-the-blank exercise.

• Next: finished a course but can't solve problems from scratch?
• Start Learning Mode for a guided warm-up