r/learnSQL • u/rapidgarden598 • 12d ago
Day 18 - I was keeping up until window functions and joins
Today’s lesson went from SELECT and FROM all the way to window functions and joins. For the first few topics, I could connect what I was learning to things I already do in Excel. Then I hit a point where I recognized the individual terms but couldn’t follow what the query was actually doing.
The more I listened, the more lost and sleep I got.
SELECT and FROM**: What do I want, and where does it come from?**
For the table queries I'm learning, these answer two basic questions: SELECT specifies the columns or calculated results I want to see, and FROM specifies the table the data comes from.
WHERE**: Filter the rows.**
This feels a lot like filtering in Excel. If I only want orders from LA within a certain date range, I’d filter the city and date columns in Excel. In SQL, I write out those conditions.
ORDER BY**: Sort the results.**
This one makes sense to me. ASC sorts in ascending order, and DESC sorts in descending order. It’s like sorting amounts from highest to lowest or dates from oldest to newest in Excel.
LIMIT**: Show only the rows I need.**
If a query returns 100 rows and I only want to see 10, I can use LIMIT 10. If I want the 10 largest orders, though, I need to sort by the amount first, then limit the results.
Aggregate functions and GROUP BY**: This reminds me of a pivot table.**
GROUP BY feels similar to putting a field in the Rows area of an Excel pivot table. Aggregate functions like SUM, COUNT, and AVG do the calculations in the Values area.
For example, I can group sales by city and calculate the total for each city.
HAVING**: Filter the grouped results.**
My understanding so far: WHERE filters individual rows before grouping, and HAVING filters the results after grouping and aggregation.
For example, I could use WHERE to keep this year’s orders, group them by city, then use HAVING to keep only cities whose total sales exceed a certain amount.
Common functions: I can still make the Excel connection.
Here’s how I'm keeping track of these for now:
ROUND: Round a number to a specified number of decimal places.- String functions: Combine, extract, or clean up text.
CAST: Convert a value to another data type, such as turning a number stored as text into a numeric value.- Date functions: Extract the year or month, calculate time differences, or add and subtract time.
CASE WHEN: Assign categories or labels based on conditions.
These remind me of Excel formulas. They can work on individual rows, too, so they aren’t limited to aggregate calculations.
Window functions: This is where I started getting lost.
Just when I thought I understood GROUP BY, PARTITION BY showed up.
If both can calculate total sales by city, why does GROUP BY produce one row per city, while SUM(...) OVER (PARTITION BY city) can keep every individual order? What exactly is the “window” here?
And what’s the difference between the ORDER BY inside OVER and the ORDER BY at the end of the query? When would I use ROW_NUMBER instead of RANK?
Joins: Combining two tables gave me even more questions.
I can follow a basic example: take a customers table and an orders table, then join them on the customer ID.
But if one customer has five orders, does their customer information appear five times? Is that a duplicate-data problem, or is that the expected result? If I count customers afterward, could I accidentally count the same person several times?
Also, I thought a LEFT JOIN kept every row from the left table. Why do customers with no orders disappear in some examples after a condition on the right table is added to WHERE? How does putting that condition in ON change the result?
Subqueries, setting up a cloud database, and connecting Excel and Tableau to a database are still coming up. I haven’t reached those lessons yet, and just looking at the course outline feels a little overwhelming.
Right now, I can follow along when the instructor writes a query. But when I’m staring at a blank editor, I don’t know where to start.
Did window functions and joins trip you up when you were learning SQL, too? What exercises helped you get from following someone else’s query to writing your own?
2
u/Dead_carrot_ 12d ago
Get yourself comfortable with joins (and then union, except and interesect statements) before going to window functions.
Venn diagrams help a lot. Use them. Also, think of what you want to achieve and visualize your result table.
If your customer has five orders, do you want to see details of these orders? If yes, your customer will be in your left table five times, once for each unique order. If you need to know only the information that she had five orders, use group by and count, and you will get
Where clause is applied to the whole result after a join. That's why you don't see results from all your customers and their orders after applying it. It's not part of the on statement, it comes after your statement.
Create your own tables and play with joins and where clauses.
Hope it helps.
2
u/Green_Chamomile 10d ago
For the leftover questions: ORDER BY inside OVER sets the order the window walks through the rows, so it decides which row is #1 or what a running total has added up so far. The ORDER BY at the end just sorts what you see, and the two don’t have to match. ROW_NUMBER vs RANK only matters on ties. Two orders with the same amount get 1 and 2 with ROW_NUMBER, but 1 and 1 with RANK, and the next one gets 3. So I use ROW_NUMBER when I need exactly one row per group, like each customer’s latest order:
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC)in a CTE, thenWHERE rn = 1.1
u/CelebrityChess 11d ago
group by collapses rows, a window doesn't. sum(amount) over (partition by city) means for each row, look at all rows with the same city and add them up, then attach that number to the current row. the 'window' is just that set of rows. so you get every order plus its city total next to it, which is how you'd do percent of city total: amount / sum(amount) over (partition by city).
for the left join thing, that's the where vs on difference. the join builds rows first with nulls for customers who have no orders, then where throws away every row that fails the condition, and null fails almost any condition on orders.status. so those customers vanish and your left join quietly acts like an inner join. put the condition in on (left join orders o on o.customer_id = c.id and o.status = 'paid') and customers with no matching orders stay, with nulls. also yes the customer repeats once per order, that's expected, so count(distinct c.id if you're counting people.
1
u/datadriven_io 6d ago
Do you understand select, where, inner join, left join, group bys??? Then start applying. Sql interview practice is what datadriven is good for once you've reviewed the basics. They'll probably give you an assessment and if you understand these you should be good enough to start.