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.

Aggregations and GROUP BY Traps

SQL aggregations, SUM, COUNT, AVG, MIN, MAX, are straightforward until GROUP BY gets involved, and then a handful of traps catch almost everyone at some point. The most common: counting rows when you meant to count distinct values, grouping by the wrong column and silently getting wrong numbers, and writing a HAVING clause where you needed a WHERE. This post covers all of them with examples.

How aggregations actually work
The COUNT trap: rows vs distinct values
The missing column trap
HAVING vs WHERE: filtering before and after grouping
How aggregations handle NULL
The double-counting trap from bad joins
GROUP BY position vs column name
What people ask about aggregations and GROUP BY

How aggregations actually work

An aggregation collapses multiple rows into a single value. SUM adds them up, COUNT counts them, AVG divides the total by the count, MIN and MAX pull the extremes. Without GROUP BY, the aggregation runs across the entire table and returns one row. With GROUP BY, it runs once per group and returns one row per group.

-- One row: total revenue across all orders SELECT SUM(order_total) AS total_revenue FROM orders; -- One row per customer: each customer's total SELECT customer_id, SUM(order_total) AS total_revenue FROM orders GROUP BY customer_id;

That's the foundation. Where it gets people is the rules that follow from it, and the silent failures when those rules get broken.

These traps show up constantly in the queries analysts actually run, which is where most of them bite in practice.

The COUNT trap: rows vs distinct values

COUNT(*) counts rows. COUNT(column) counts non-NULL values in that column. COUNT(DISTINCT column) counts unique non-NULL values. These produce different numbers and it matters which one you use.

SELECT COUNT(*) AS total_rows, COUNT(customer_id) AS non_null_customer_ids, COUNT(DISTINCT customer_id) AS unique_customers FROM orders;

If a customer placed 5 orders, COUNT(*) counts all 5 rows. COUNT(DISTINCT customer_id) counts that customer once. If you're trying to answer "how many customers ordered this month," COUNT(*) gives you the wrong answer and you won't know it unless you're thinking carefully.

I've seen this one trip up analysts at every experience level. The query runs, returns a number, and nobody questions it because there's no error message. The wrong answer just goes into a dashboard.

The fix is to be explicit about what you're counting before you write the query. Rows? Distinct entities? Events per entity? Each one is a different function.

The missing column trap

Most SQL databases require that every column in your SELECT list either appears in GROUP BY or is inside an aggregation function. Break that rule and you get an error, which is actually helpful, because it forces you to think about what you're asking for.

-- This errors in most databases: SELECT customer_id, customer_name, SUM(order_total) FROM orders GROUP BY customer_id; -- customer_name must be in GROUP BY or aggregated: SELECT customer_id, customer_name, SUM(order_total) FROM orders GROUP BY customer_id, customer_name;

MySQL with certain settings is the exception, it'll pick an arbitrary value for the non-grouped column rather than throwing an error. That's the more dangerous behavior because you get a result that looks valid but is pulling a random customer_name for each group. If your queries run fine in MySQL but produce unexpected output, check whether this mode is on.

The right instinct when you hit a GROUP BY error: ask which columns actually define the group. If you're grouping revenue by customer, the group is the customer, and if customer_id uniquely identifies a customer, that's all you need. Add customer_name only if it adds information, and include it in GROUP BY when you do.

HAVING vs WHERE: filtering before and after grouping

WHERE filters rows before grouping. HAVING filters groups after grouping. They look similar and they sit near each other in the query, which is why people mix them up.

-- WHERE: filter individual rows before the GROUP BY runs SELECT customer_id, SUM(order_total) AS total FROM orders WHERE order_date >= '2025-01-01' GROUP BY customer_id; -- HAVING: filter groups after the GROUP BY runs SELECT customer_id, SUM(order_total) AS total FROM orders GROUP BY customer_id HAVING SUM(order_total) > 1000;

You can't use HAVING to filter on a raw column value, that's WHERE's job. And you can't use WHERE to filter on an aggregate result like SUM(order_total), that's HAVING's job.

The order SQL processes these clauses: FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY. Keeping that sequence in mind clears up most of the confusion. WHERE runs before groups exist, so it can't reference aggregates. HAVING runs after groups exist, so it can.

You can use both in the same query:

SELECT customer_id, SUM(order_total) AS total FROM orders WHERE order_date >= '2025-01-01' -- filter rows first GROUP BY customer_id HAVING SUM(order_total) > 1000; -- then filter groups

This is the pattern for "customers who spent over $1,000 this year." WHERE limits it to this year. HAVING limits it to high spenders.

How aggregations handle NULL

Aggregation functions ignore NULL values. SUM ignores NULLs. AVG ignores NULLs (which affects the denominator, it's the average of non-NULL values, not all values). COUNT(column) ignores NULLs. COUNT(*) does not ignore NULLs because it counts rows, not column values.

-- If discount has NULLs, AVG only averages the non-NULL rows SELECT AVG(discount) AS avg_discount FROM orders; -- To treat NULL as zero in the average: SELECT AVG(COALESCE(discount, 0)) AS avg_discount FROM orders;

The AVG trap is the one that bites most: if 40% of your rows have NULL in the discount column, your AVG(discount) is the average of the 60% that have a value, not the average across all orders. Whether that's correct depends on what NULL means in your data. If NULL means "no discount applied," COALESCE to zero. If NULL means "discount unknown," leave it and document the caveat.

NULL also affects GROUP BY itself. Rows where the GROUP BY column is NULL get grouped together into a NULL bucket. That's usually fine, but worth knowing, especially if a large number of NULLs in an ID column are quietly getting lumped into a single "unknown" group.

The double-counting trap from bad joins

This one is common and easy to miss. If you JOIN a table that has multiple rows per entity before aggregating, your SUM inflates.

-- orders table: one row per order -- order_items table: multiple rows per order -- This overcounts revenue because each order joins to multiple items SELECT o.customer_id, SUM(o.order_total) AS total FROM orders o JOIN order_items oi ON o.order_id = oi.order_id GROUP BY o.customer_id;

If an order has 3 line items, it joins to 3 rows in order_items. SUM(o.order_total) then adds that order's total 3 times. The result looks like valid SQL and runs without error, but the numbers are wrong.

The fix depends on what you actually need:

If you only need order-level totals, don't join the line-item table at all. Aggregate the orders table on its own, since order_total already lives there.
If you need both, pre-aggregate the line items first. Roll order_items up to one row per order in a subquery or CTE, then join that back to orders.
If you must keep the join, aggregate a de-duplicated value. Sum order_total over DISTINCT order_id, or move the SUM to a level where each order is counted once.

The rule of thumb: aggregate after joining only when the join is one-to-one or many-to-one. When a join is one-to-many and you're summing from the "one" side, you're heading toward double counting.

The double-counting problem usually starts with a bad join quietly inflating your counts before the aggregation ever runs.

GROUP BY position vs column name

Most SQL databases let you GROUP BY column position instead of column name, GROUP BY 1, 2 instead of GROUP BY customer_id, month. It's shorter and works. The trap is that position-based references break silently when you reorder your SELECT list.

-- This groups by customer_id and month SELECT customer_id, month, SUM(revenue) FROM sales GROUP BY 1, 2; -- After reordering SELECT, GROUP BY 1, 2 now groups by month and customer_id -- Same result here, but in other cases the change silently affects output SELECT month, customer_id, SUM(revenue) FROM sales GROUP BY 1, 2;

In production code or shared queries, name the columns explicitly. GROUP BY customer_id, month is unambiguous regardless of SELECT order. Position references are fine for quick ad hoc work, but they're a maintenance risk in anything that gets reused.

If you're building toward a role where you'll write production SQL, anything in dbt, anything in a BI tool that others rely on, anything that runs on a schedule, explicit column names are the habit to build now. The Analyst Hive program covers exactly this kind of production-ready SQL practice as part of the daily structure.

What people ask about aggregations and GROUP BY

Why does my GROUP BY query return more rows than expected?

Usually because you're grouping by more columns than you intended, or because a join introduced extra rows before the GROUP BY ran. Check that the columns in your GROUP BY actually define the grain you want. Adding a column you don't need to GROUP BY splits groups that should be combined.

Can I GROUP BY a calculated column?

Depends on the database. BigQuery and PostgreSQL let you reference a SELECT alias in GROUP BY. SQL Server and MySQL generally don't, you'd need to repeat the expression or wrap the query in a CTE. The safest approach across dialects is to put the calculation in a CTE first, then GROUP BY the column name.

What's the difference between COUNT(*) and COUNT(1)?

Functionally identical on every modern database. COUNT(1) is sometimes written as a habit from older SQL where COUNT(*) had a performance cost, that hasn't been true for a long time. Use COUNT(*) because it reads clearly; it's the idiomatic choice and signals "count rows" without ambiguity.

Why is my SUM returning NULL instead of a number?

SUM of an empty set returns NULL, not zero. If no rows match your WHERE filter, SUM returns NULL. Wrap it in COALESCE(SUM(column), 0) if you need zero instead of NULL for downstream calculations or dashboard display.

Can I use an aggregate in a WHERE clause?

No. WHERE runs before GROUP BY, so aggregates don't exist yet at that point. Move the filter to HAVING, or wrap the aggregation in a CTE and filter the outer query. Trying to filter on SUM or COUNT in WHERE is one of the most common syntax errors for people learning GROUP BY.

Why does GROUP BY on a text column produce unexpected groupings?

Case sensitivity. Depending on the database and collation, "Active" and "active" may or may not be treated as the same group. Snowflake is case-insensitive by default for string comparisons but stores values as written. PostgreSQL is case-sensitive. If your groups look wrong, check for case variation and standardize with LOWER() or UPPER() before grouping.

Aggregation errors are frustrating because the query runs. There's no red error message telling you the number is wrong, just a result that looks plausible until someone checks it against another source. The habit that prevents most of these is writing the expected output before writing the query: if I expect one row per customer with their total spend, does my GROUP BY actually produce that?

If you're building SQL skills as part of a data analytics job search, Analyst Hive structures daily practice around exactly this kind of query-writing fluency, the stuff that shows up in take-home assessments and technical interviews. Community is at skool.com/analysthive/about.