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.

SQL Self-Joins Explained: How to Join a Table to Itself (With Examples)

A self-join is a regular join where you join a table to itself. You give the table two different aliases, treat them as if they were two separate tables, and join them on a column that relates one row to another. You reach for a self-join when the rows you want to compare live in the same table, like matching each employee to their manager when both sit in one employees table.

That is the whole idea. Nothing about the join is special. The only trick is the aliases, because without two names for the same table the database has no way to tell which copy you mean. The rest of this post covers when you actually need a self-join, how to write one step by step, how it differs from a normal join, and the mistakes that quietly double your row counts.

What is a self-join in SQL?
Why would you join a table to itself?
How to write a self-join step by step
Self-join vs a regular join
When to use a LEFT self-join
Common mistakes and how to avoid them
FAQ

What is a self-join in SQL?

A self-join is a query that joins a table to itself. It is not a separate type of join like INNER or LEFT. It is a normal join where both sides of the join point at the same table, and you tell them apart with aliases.

Picture an employees table with 3 columns: id, name, and manager_id. The manager_id points at the id of another row in the same table, because a manager is also an employee. If you want a result that shows each employee next to their manager's name, the manager's name is not in a second table. It is one row up in the table you already have. A self-join is how you pull it across.

You write it by aliasing the table twice, usually something short like e for employee and m for manager, then joining e.manager_id to m.id. The database loads two logical copies of the table and matches rows between them. Same table, two aliases, one join condition.

Why would you join a table to itself?

You join a table to itself whenever the rows you want to compare or connect are in the same table. This comes up more than people expect, and it's one of the patterns I use most at work.

The common cases:

Hierarchies. Employee to manager, category to parent category, comment to parent comment. One row references another row in the same table through an ID column.
Comparing rows to other rows. Finding every pair of customers in the same city, or every product cheaper than another product in the same category. You join the table to itself and compare across the two aliases.
Finding duplicates. Join a table to itself on the columns that should be unique, like email, and keep pairs where the IDs differ. Any match is a duplicate.
Row-to-row sequences without window functions. Matching each record to the one before it, like this month's revenue next to last month's. Window functions are usually cleaner for this now, but a self-join still works and shows up in older code constantly.

In data engineering today I work in Snowflake, and self-joins show up most when I model hierarchies. An org chart, a category tree, a chart of accounts. The parent and the child are the same kind of thing, so they live in the same table, and a self-join is the natural way to walk one level of that relationship.

How to write a self-join step by step

You write a self-join the same way you write any join, with one rule you can't skip: every reference to the table needs an alias. Here is the order I think through it.

Alias the table twice. List the table once, give it an alias, then join it to itself with a second alias. Use names that mean something, like e and m for employee and manager, not t1 and t2.
Write the join condition that links the two rows. This is the column that relates one row to the other. For employee to manager, it is e.manager_id = m.id. This is the line that makes it a self-join.
Select from both aliases with the alias prefix. Every column has to say which copy it comes from, like e.name AS employee, m.name AS manager. If you forget the prefix on a column that exists in both, the database throws an ambiguous column error.
Pick INNER or LEFT based on what you want at the top of the hierarchy. More on this below, because it's the choice people get wrong.

Put together, a self-join to show each employee and their manager reads as: SELECT e.name AS employee, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.id. Two aliases, one join condition, columns prefixed. That is the entire pattern.

If writing clean joins under pressure is the part you want to drill, that's exactly the kind of daily SQL practice built into Analyst Hive, because joins are where most entry-level analysts freeze in a technical screen.

Self-join vs a regular join

The mechanics are identical. A self-join and a regular join both match rows from one source to rows from another using a condition. The difference is only that in a self-join both sources are the same table.

What changes in practice is small but real:

Aliases stop being optional. In a two-table join you can often skip aliases because the column names are unique across the tables. In a self-join both sides have the exact same column names, so aliases are the only way to tell them apart.
You have to think about direction. With two tables the relationship is obvious. With one table joined to itself, you decide which alias is the parent and which is the child, and getting that backward returns the wrong side of the relationship.
Duplicate and self-match risk goes up. A row can match itself, or a pair can show up twice in both orders. You control that with the join condition, which I cover in the mistakes section.

So a self-join is not a harder concept than a regular join. It is the same concept pointed at one table, with aliases doing the work of keeping the two copies straight.

When to use a LEFT self-join

Use a LEFT self-join when you want to keep rows that have no match on the other side. The classic case is a hierarchy with a top. The CEO has no manager, so their manager_id is NULL.

If you write the employee-to-manager query with an INNER join, the CEO drops out of the result, because there is no row whose id matches their NULL manager_id. An INNER join only keeps rows that match on both sides, and the top of the tree matches nothing. People stare at a result that's missing one person and cannot work out why.

Switch it to a LEFT join from the employee alias to the manager alias, and the CEO stays in, with a NULL manager name you can label as something like "No manager" with COALESCE. The rule is simple: if the top of your hierarchy matters, use LEFT. If you only want rows that actually have a parent, INNER is correct and dropping the top is what you want.

Common mistakes and how to avoid them

Most self-join bugs are not syntax errors. They run fine and return a result that looks plausible and is wrong. These are the ones I see most from the people who message me about their SQL.

Forgetting the aliases. Without two aliases the query can't reference the two copies, and you get an error or, worse, a query that does not mean what you think. Always alias both sides.
Rows matching themselves. When you join a table to itself on a shared value, like city, every row matches itself because its own city equals its own city. Add a condition like e1.id <> e2.id to drop self-matches.
The same pair appearing twice. Matching customers in the same city returns both (A, B) and (B, A). If you only want each pair once, use e1.id < e2.id instead of <>, which keeps one ordering and drops the mirror.
Using INNER when the top of the hierarchy matters. This silently drops the root row, like the CEO with a NULL manager_id. Use a LEFT join when you need to keep it.
Vague aliases. Naming them a and b on a hierarchy query makes it impossible to tell parent from child when you reread it next week. Name them for their role, like child and parent, or e and m.

The fix for almost all of these lives in the join condition. A self-join that returns too many rows usually needs a sharper ON clause, not a different kind of join.

FAQ

Is a self-join a special type of join?

No. A self-join is a regular INNER or LEFT join where both sides point at the same table. There is no SELF JOIN keyword. You write FROM table a JOIN table b and the fact that both are the same table is what makes it a self-join. The join types and rules you already know all apply.

Do you have to use aliases in a self-join?

Yes. Aliases are the one part you can't skip. Both copies of the table have identical column names, so without a distinct alias on each side the database can't tell which copy you mean and returns an ambiguous column error. Give each side a short, meaningful alias and prefix every column with it.

Can a self-join use LEFT JOIN instead of INNER JOIN?

Yes, and often you want it to. Use a LEFT self-join when some rows have no match on the other side and you still want to keep them, like the top of a hierarchy whose parent ID is NULL. An INNER self-join drops those rows, which is sometimes correct and sometimes a silent bug.

How do you avoid rows matching themselves in a self-join?

Add a condition that excludes the row from matching its own copy. Use a.id <> b.id to drop self-matches entirely, or a.id < b.id when you're pairing rows and want each pair only once instead of twice in both orders. The right one depends on whether you want pairs or a filtered list.

When should you use a window function instead of a self-join?

Use a window function when you're comparing a row to a nearby row in a sequence, like this month versus last month. LAG and LEAD do that more cleanly than a self-join and usually run faster. A self-join is still the right tool for hierarchies and for pairing rows that are not in a simple order.

Are self-joins slow?

Not inherently. A self-join performs like any join on the same data, and a good index on the join columns keeps it fast. It gets slow for the same reasons any join does: joining on unindexed columns, or a weak join condition that produces far more pairs than you need. Tighten the ON clause and index the keys.

Where to go from here

Self-joins feel strange the first time because you're pointing a join at one table, but the pattern is small: two aliases, one join condition, and a clear choice between INNER and LEFT. Get those three right and the headache goes away. The harder part is recognizing fast, mid-query, that the rows you need are sitting in the same table. That recognition is a reps problem, and building those reps on real tables is what Analyst Hive is built for: a 90-day daily-task program for aspiring data analysts who want SQL that holds up when someone is watching.