How much of a data analyst's day is spent writing SQL?

Most of it. On a typical day, 3 to 5 hours go into writing, testing, and fixing SQL queries. That's the bulk of the work, not a side skill. The queries aren't glamorous—you're pulling a table, checking if the numbers match what someone expects, joining it to another table, and formatting it so a non-technical person can read it.

Do data analysts use Python every day?

Not at the entry level, and often not later either. SQL and a spreadsheet tool cover most entry-level and mid-level analyst work. Python shows up more in data engineering, machine learning, or advanced analytics roles, not in the daily grind of a standard analyst seat.

What meetings does a data analyst attend?

A typical week includes a team standup, one or two stakeholder meetings where you present or explain a number, and the occasional call where someone wants to understand a dashboard they can't read on their own. The skill that matters most in these meetings is explaining a number in one sentence to someone who doesn't care how you got it.

Is being a data analyst boring?

Some of it, yes. Most days include stretches of repetitive query writing and data cleanup that nobody would call exciting. The interesting part shows up when a chart finally answers a question someone's been asking for weeks, or when you catch an error before it reaches a director's inbox.

What's the difference between a junior and senior analyst's day?

A junior analyst spends more time on execution: writing the queries, building the dashboard, fixing the data someone else flagged. A senior analyst spends more time deciding what should get built in the first place and pushing back when a request doesn't make sense. Both still write SQL most days. The senior version just spends less time on syntax and more time on judgment.

What tools does a data analyst use daily?

In order of frequency: SQL editor, a BI tool like Tableau or Looker, spreadsheets like Excel or Google Sheets, Slack or email, and Python (occasionally). SQL editor is used most, while Python ranks lower than many expect, especially at the entry level.

CTEs vs Subqueries and Which to Write

Use a CTE when you're writing something you'll need to read, reuse, or debug later. Use a subquery when the logic is simple enough to sit inline without making the outer query hard to follow. That's the practical rule. The rest of this post explains why, and what changes as the query gets more complex.

If you're still early, it's worth knowing how much SQL an entry-level role actually expects before worrying about structuring complex queries.

What is a CTE?

A CTE, or Common Table Expression, is a named temporary result set you define before your main query using the WITH keyword. It exists only for the duration of that query. You write it once at the top, give it a name, and reference it by that name in the SELECT below.

WITH monthly_totals AS ( SELECT customer_id, DATE_TRUNC('month', order_date) AS month, SUM(order_total) AS total FROM orders GROUP BY 1, 2 ) SELECT customer_id, month, total FROM monthly_totals WHERE total > 500;

The CTE runs first, produces a result set named monthly_totals, and then the outer SELECT treats that like any other table. Nothing gets stored anywhere. It's gone when the query finishes.

You can chain several CTEs in sequence, each one building on the last:

WITH step_one AS ( SELECT ... ), step_two AS ( SELECT ... FROM step_one ) SELECT * FROM step_two;

That chaining is where CTEs earn their place. A complex transformation broken into named steps is readable. The same logic collapsed into nested subqueries is a slog to follow.

What is a subquery?

A subquery is a SELECT statement nested inside another query. It can live in the FROM clause as an inline view, in the WHERE clause as a filter, or in the SELECT list as a scalar subquery that returns a single value.

SELECT customer_id, month, total FROM ( SELECT customer_id, DATE_TRUNC('month', order_date) AS month, SUM(order_total) AS total FROM orders GROUP BY 1, 2 ) AS monthly_totals WHERE total > 500;

Same result as the CTE version, and the logic is identical. The difference is that the subquery has no name until you reach the alias at the bottom, AS monthly_totals, so you're reading the code before you know what it produces. With a CTE, the name comes first and sets up what you're about to read.

The same query, both ways

Here's a realistic example: find customers whose most recent order was over $200, then pull their lifetime total.

With CTEs:

WITH latest_orders AS ( SELECT customer_id, MAX(order_date) AS last_order_date FROM orders GROUP BY customer_id ), recent_high_value AS ( SELECT o.customer_id FROM orders o JOIN latest_orders l ON o.customer_id = l.customer_id AND o.order_date = l.last_order_date WHERE o.order_total > 200 ), lifetime_totals AS ( SELECT customer_id, SUM(order_total) AS lifetime_value FROM orders GROUP BY customer_id ) SELECT r.customer_id, lt.lifetime_value FROM recent_high_value r JOIN lifetime_totals lt ON r.customer_id = lt.customer_id;

With subqueries:

SELECT r.customer_id, lt.lifetime_value FROM ( SELECT o.customer_id FROM orders o JOIN ( SELECT customer_id, MAX(order_date) AS last_order_date FROM orders GROUP BY customer_id ) l ON o.customer_id = l.customer_id AND o.order_date = l.last_order_date WHERE o.order_total > 200 ) r JOIN ( SELECT customer_id, SUM(order_total) AS lifetime_value FROM orders GROUP BY customer_id ) lt ON r.customer_id = lt.customer_id;

Both run. The CTE version is something a colleague can read tomorrow. The subquery version makes you read inside out, track which alias belongs to which nested layer, and assemble what the query does in your head before you reach the outer SELECT.

I work in Snowflake and Coalesce daily. Any time a transformation has more than a couple of intermediate steps, it goes into CTEs. Subqueries aren't wrong. Readable SQL is just easier to maintain, debug, and hand off.

When to write a CTE

Default to a CTE when any of these are true:

The query has multiple steps. If you're staging a result, then filtering it, then joining it to something else, give each step a named CTE. The query reads top to bottom in the order the logic runs.
You reference the same logic more than once. Define it as a CTE once and reference the name wherever you need it, instead of pasting the same subquery into two places where the copies can drift apart.
Someone else will read or maintain the query. Anything going into a dbt model, a Coalesce transformation, a scheduled report, or a shared repo should be readable by the next person who opens it, and that's usually not you.
You're debugging. With CTEs you can run them one at a time, selecting from each named step to check its output before moving on. Nested subqueries force you to untangle the whole thing at once.
The nesting is getting deep. Once you're two or three subqueries deep, aliases stop being obvious and the query gets hard to hold in your head. Flatten it into named CTEs.
You need recursion. This one isn't a style call. Walking hierarchical data requires a recursive CTE, covered below.

CTEs and window functions pair constantly in real analyst SQL, so it helps to be comfortable with both.

When a subquery is fine

Subqueries are the right call when the logic is contained and the query stays readable:

A simple filter in WHERE. Something like WHERE customer_id IN (SELECT customer_id FROM vip_list) is clear on one line and doesn't need a CTE wrapped around it.
A scalar value in SELECT. Pulling a single number alongside each row, like a company-wide average to compare against, reads fine as an inline subquery.
A one-off inline view you use once. If a derived table appears a single time and the query is still short, a subquery in FROM keeps everything in one place without the overhead of a named block.

The test is whether the query stays readable. If you have to scroll up and down to match a subquery alias to its definition, that's the signal to switch to CTEs.

Performance: does it actually matter?

For most queries on most modern databases, CTEs and subqueries perform the same. The query optimizer treats them identically. On Snowflake, BigQuery, PostgreSQL, and SQL Server, the execution plan for a CTE and an equivalent subquery is usually the same.

The exception worth knowing: in some older versions of PostgreSQL and in MySQL, a CTE was treated as an optimization fence, meaning the planner couldn't push filters into the CTE. PostgreSQL fixed this in version 12. MySQL's CTE support is still more limited.

On modern cloud warehouses, don't pick between CTEs and subqueries for performance. Pick for readability. If a specific query is slow, look at indexes, partition pruning, and join order before blaming the CTE syntax.

Recursive CTEs: the one thing subqueries can't do

There's one scenario where a CTE isn't a style preference, it's the only tool. A recursive CTE lets a query reference itself, which is how you traverse hierarchical or graph-structured data.

WITH RECURSIVE org_chart AS ( -- Anchor: start with the top-level employee SELECT employee_id, manager_id, name, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL -- Recursive step: join each employee to their manager SELECT e.employee_id, e.manager_id, e.name, oc.level + 1 FROM employees e JOIN org_chart oc ON e.manager_id = oc.employee_id ) SELECT * FROM org_chart ORDER BY level;

This walks an org chart from the top down, assigning a level to every employee. A subquery can't do this because it can't reference itself. If you're working with parent-child relationships, category trees, or network graphs in SQL, a recursive CTE is the right tool.

Recursive CTEs come up less often in day-to-day analyst work than ROW_NUMBER or LAG, but they show up in interviews because they separate people who've read about SQL from people who've actually written it. Worth knowing.

If you're working through SQL like this as part of breaking into data analytics, the Analyst Hive program structures this kind of practice into a daily plan, covering the SQL patterns that come up in analyst interviews rather than every SQL feature that exists.

What people ask about CTEs vs subqueries

Are CTEs faster than subqueries?

On modern databases like Snowflake, BigQuery, and PostgreSQL 12 and up, no. The query optimizer treats them the same and compiles both into the same execution plan in most cases. Choose based on readability, not an assumed performance difference. If you're seeing slowness, the bottleneck is almost never the CTE syntax itself.

Can I reference a CTE multiple times in the same query?

Yes, and that's one of the main reasons to use one. You write the logic once, name it, and reference it as many times as you need in the rest of the query. With a subquery you'd duplicate the whole block every time, which creates drift risk when one copy gets updated and the other doesn't.

Can a CTE reference another CTE?

Yes. You can chain as many CTEs as you need, each referencing the ones defined before it. This is the pattern for multi-step transformations: define each step as its own named CTE and compose them in sequence. The optimizer sees the full chain and can optimize across it.

What's the difference between a CTE and a temp table?

A CTE exists only for the duration of a single query and isn't stored anywhere. A temp table is written to disk or memory and can be referenced across several queries in the same session. Use a CTE for query-level logic, and a temp table when you need the intermediate result available across later queries.

Do all SQL databases support CTEs?

Most modern ones do. PostgreSQL, SQL Server, BigQuery, Snowflake, Redshift, and DuckDB all support CTEs fully. MySQL added support in version 8.0, and SQLite has had it since version 3.8.3. An older MySQL version is the main exception to watch for.

When would I use a subquery in the SELECT clause?

When you need a single value alongside each row, like the company-wide average order total or the max value in a category, a scalar subquery in SELECT can be cleaner than joining to a separate CTE. The constraint: it must return exactly one row and one column, or the query errors. If it could return more than one row, use a CTE and join instead.

Short version: write CTEs by default for anything with multiple steps, anything you'll reuse, and anything that lives in a production pipeline. Subqueries are fine for simple filters and scalar values where the logic fits in a line or two without hurting readability.

The Analyst Hive program is a daily-task structure for breaking into data analytics, covering SQL, Excel, BI tools, portfolio projects, and interview prep. If you're building toward your first analyst role, join Analyst Hive.