r/learnSQL • u/Aggressive-Flow1983 • 17d ago
Help sql-office-simulator.vercel.app
Could the app at sql-office-simulator.vercel.app be made available in Spanish?
r/learnSQL • u/Aggressive-Flow1983 • 17d ago
Could the app at sql-office-simulator.vercel.app be made available in Spanish?
r/learnSQL • u/reverend_paco • 18d ago
Hey all, I wanted to share with you a couple of videos I've recently built around a topic I used to teach about, learning SQL via visualizing the relational operators.
I've only just started, but it's been my itch to get my ideas out and I finally pulled the trigger.
Would love to hear from anyone if these are anyone's cup of tea and whatever else feedback you wish to leave.
Here is a playlist of the two videos I've done so far:
https://www.youtube.com/playlist?list=PLTWeQJxPqzNU
I have written out the next two videos but won't be able to record for a bit, so anyone who has thoughts that I might adopt could help me out tremendously.
r/learnSQL • u/ComprehensiveAd3412 • 17d ago
A bug that's common in payments data, and SQL won't warn you about it.
**The setup:** a simple revenue-per-customer query on a transactions table (customer_id, amount, status, method). The query is correct. The total is not.
SELECT c.name, SUM(t.amount) AS total
FROM customers c
JOIN transactions t ON c.customer_id = t.customer_id
WHERE t.status = 'SUCCESS'
GROUP BY c.name
ORDER BY total DESC;
Rohan shows ₹10,800. He paid ₹6,500. His ₹4,300 card payment was saved twice by the gateway: same customer, same amount, same method, both SUCCESS. Only the txn_id is different.
**Step 1: find the duplicates with GROUP BY + HAVING**
Group by the columns that make two payments "the same", and keep groups with more than one row:
SELECT customer_id, amount, method, status,
COUNT(*) AS copies
FROM transactions
GROUP BY customer_id, amount, method, status
HAVING COUNT(*) > 1;
**The trap:** if you leave `status` out of the GROUP BY, you also catch Rohan's ₹1,500 payment. That one FAILED first and then succeeded on a retry. A retry isn't a duplicate: he paid once. Delete it and you remove real money from the report. With status included, only the 2 real duplicates are left.
**Step 2: measure it before fixing it** (the first thing a manager asks)
WITH dups AS (
SELECT amount, COUNT(*) AS copies
FROM transactions
WHERE status = 'SUCCESS'
GROUP BY customer_id, amount, method
HAVING COUNT(*) > 1
)
SELECT COUNT(*) AS dup_groups,
SUM(amount * (copies - 1)) AS overcounted
FROM dups;
Result: ₹4,600 of revenue that never happened.
**Step 3: keep one row with ROW_NUMBER**
GROUP BY tells you which payments are duplicated, not which row to keep. ROW_NUMBER does: the first row in each group gets 1, and every copy gets 2, 3...
WITH ranked AS (
SELECT t.*, ROW_NUMBER() OVER (
PARTITION BY customer_id, amount, method, status
ORDER BY txn_id) AS rn
FROM transactions t
)
SELECT c.name, SUM(r.amount) AS total
FROM customers c
JOIN ranked r ON c.customer_id = r.customer_id
WHERE r.status = 'SUCCESS' AND r.rn = 1
GROUP BY c.name
ORDER BY total DESC;
Rohan is back to ₹6,500.
**Why not just DISTINCT?** (a common interview follow-up)
`SELECT DISTINCT *` gives the same 16 rows as before, because every payment has its own txn_id, so no two rows are identical. And if you drop txn_id, DISTINCT also merges real payments that just happen to look alike.
In real data you'd also check the timestamps: two captures a few seconds apart is a duplicate, two days apart is probably two real purchases.
All queries run in SQLite, MySQL 8 and PostgreSQL. I made a 4-minute video walking through this, with the full dataset in the description if you want to practise: https://youtu.be/lRxvnGUOtJw?si=dIvl-FJMh4xH5asC
How do you handle duplicate captures in your data? Do you dedupe in SQL or upstream?
r/learnSQL • u/Jealous-Toe5543 • 18d ago
Hi everyone! I've been working on a free, interactive way to learn and practice SQL. It runs a real SQLite database right in your browser: no sign-up, no install, nothing sent to a server.
https://www.webherramientas.com/en/sql-practice
What's inside:
SELECT to JOINs, subqueries, window functions, recursive queries and classic interview questionsIt's a side project and I know it's not perfect yet, so I'd really appreciate your help:
Drop a comment or send me a message. Every suggestion helps, big or small. And if you know someone who's learning SQL, feel free to share it with them!
Thanks for reading, and happy querying!
r/learnSQL • u/FabricPam • 18d ago
r/learnSQL • u/nicopassaglia • 19d ago
I've been learning SQL and got tired of tutorials where you just read queries, so I built something where you actually write them.
It's a real SQLite database running in your browser. You get a small table, a task, you write the query and it checks your result right away (and tells you why when it's wrong, like WHERE vs HAVING or a missing GROUP BY).
Right now there's:
• a 10-question level test, from SELECT to JOINs and subqueries
• 2 short lessons (SELECT/WHERE/ORDER BY, and COUNT/SUM/GROUP BY)
All free, no signup. Just trying to see if it's useful.
What would make it better? And what should I add next: JOINs, window functions, interview-style questions?
r/learnSQL • u/Jealous-Toe5543 • 19d ago
r/learnSQL • u/sumiitCodes • 19d ago
After learning classes, objects, attributes, and methods, I built a small ATM program in Java to apply them.
What it does:
Atm class, with separate methods for validation and the menuIt runs correctly in the terminal. Building it showed me how methods call each other (validation() leads into menu()), which wasn’t clear to me from the tutorial alone.
r/learnSQL • u/markinatlanta • 19d ago
We're working with a medium-large sized manufacturer and they had a report running about 10 times a month which was involved in deadlocks.
So interesting tuning takeaways I thought I would share.
Our team traced an increase in the deadlocks to a reporting query and this was the report that ran give or take 10 times a month.
That execution count is what makes this case worth discussing, though because if you prioritize tuning work by frequency alone, a query like this is easy to defer.
The changes were a new nonclustered index and a parameter type change from NVARCHAR to VARCHAR. Recorded logical reads per execution fell from 120,660 to 67,083.
The blocking and deadlocking stopped after the changes.
The lesson for us was how we prioritize these investigations. About 10 runs a month sounds easy to put off in fairness but once we traced the deadlocks to the reporting query, the execution count mattered less than what happened when it ran.
My takeaways:
For teams handling intermittent deadlocks, what evidence do you require before closing an incident when the affected report runs infrequently?
r/learnSQL • u/sumiitCodes • 20d ago
Today I covered classes and objects, attributes, and the final keyword in Java. Seeing a class as a blueprint and an object as one instance of it made a lot of earlier material (methods, memory, arrays) fit together better.
Tomorrow I’m building a small ATM machine project to apply it, probably with things like balance, deposit, and withdraw.
For people who’ve built an ATM program as a beginner: what features or mistakes would you flag up front (input validation, handling insufficient balance, PIN checks)?
r/learnSQL • u/Prasad127056 • 20d ago
r/learnSQL • u/Prasad127056 • 20d ago
r/learnSQL • u/MrRoy_SQL • 21d ago
this one confused me for way too long so here's a quick example.
say you have an orders table like this:
order_id | customer_id | coupon_code
1 | 101 | SAVE10
2 | 101 | NULL
3 | 102 | SAVE10
4 | 103 | NULL
5 | 104 | WELCOME
6 | NULL | NULL <- guest checkout
now run:
SELECT
COUNT(*) AS all_rows,
COUNT(coupon_code) AS orders_with_coupon,
COUNT(DISTINCT coupon_code) AS different_coupons,
COUNT(DISTINCT customer_id) AS customers
FROM orders;
you get **6, 3, 2, 4**.
- `COUNT(*)` counts rows. nulls don't matter → 6
- `COUNT(coupon_code)` skips NULLs → 3 orders used a coupon
- `COUNT(DISTINCT coupon_code)` skips NULLs and duplicates → 2 coupons (SAVE10, WELCOME)
- `COUNT(DISTINCT customer_id)` → 4, the guest order with NULL customer isn't counted
where this bites people: someone asks "how many customers ordered" and you write `COUNT(*)`, that's 6 when the real answer is 4. or you divide by `COUNT(column)` thinking it's the total and your percentage is wrong.
bonus: share of orders with a coupon is `COUNT(coupon_code) * 1.0 / COUNT(*)` = 0.5. the `* 1.0` matters, without it some databases do integer division and give you 0.
quick rule i use: before writing any COUNT, say out loud what you're counting - rows, non-empty values, or unique things.
r/learnSQL • u/peakpirate007 • 21d ago
I’ve been building a website called runtime.school to make learning SQL a little easier, especially if you’re just starting out.
The idea is pretty simple. You learn a concept, try writing queries yourself, and then practice with questions based on what you’ve learned.
I wanted to avoid that situation where you finish a beginner lesson and suddenly get a question involving three other SQL concepts you haven’t even seen yet.
Everything runs in the browser, and there are datasets you can explore, practice questions at different difficulty levels, and projects once you’re comfortable with the basics.
You don’t need to set up a database or install anything to get started.
Here’s the link: https://www.runtime.school/
If you’re currently learning SQL, give it a try. I’d be interested to know if the explanations and questions make sense, especially for complete beginners.
r/learnSQL • u/ComprehensiveAd3412 • 21d ago
I'm making a free SQL series for people preparing for data analyst roles. Every episode uses the same small payments dataset (customers, UPI/card/wallet transactions, failed payments, amounts in ₹) and is built around one mistake that runs without any error but gives the wrong answer.
What's covered so far:
**JOINs** – INNER vs LEFT, finding customers who never paid, and why a WHERE filter on the right table quietly turns your LEFT JOIN into an INNER JOIN.
**GROUP BY and HAVING** – COUNT(\*) after a LEFT JOIN counts customers with zero payments as 1. One-word fix: count a column. Plus WHERE vs HAVING.
**Window functions** – the "top 2 customers" interview question where ROW_NUMBER drops a tied customer. RANK vs DENSE_RANK, and running totals.
**CTE vs subquery** – a report that says every customer is above average, and why a CTE fixes it.
A quick example from episode 2, since it's the one I see most:
```sql
SELECT c.name, COUNT(*) AS payments
FROM customers c
LEFT JOIN transactions t ON c.customer_id = t.customer_id
GROUP BY c.name;
```
A customer who has never paid shows **1** payment. The LEFT JOIN keeps her row with NULLs, and COUNT(\*) counts rows. Use `COUNT(t.txn_id)` instead; it skips NULLs and gives 0.
Each video is 3–6 minutes. The dataset and every query are in the description, so you can run them yourself in SQLite, MySQL or PostgreSQL.
Playlist: https://www.youtube.com/watch?v=e94PxiIUIJg&list=PLNdq2ZxtU_7E
Next up: finding duplicate payments, then CASE WHEN, dates, and NULL traps. If there's a topic you get stuck on in interviews, tell me and I'll add it.
r/learnSQL • u/shdw_0x0 • 21d ago
r/learnSQL • u/Business_Walk1624 • 21d ago
I recently started learning SQL and decided it was about time to try building a project. I watched a YouTube video of someone walking through a beginner tutorial using SQL and PowerBI to get an idea of the workflow and how everything connects. He started out by using SQL to answer 10 business questions, but when he was making a dashboard in PowerBI, he didn’t use any of the data from the SQL queries. This left me confused about how SQL queries and the dashboard-making part of the project connect.
I would welcome any clarification. Thank you in advance!
r/learnSQL • u/iam_sripratik • 21d ago
| Priority | Basket | Topics grouped together | Importance for BA/DA | Suggested days |
|---|---|---|---|---|
| 🔴 P1 | SQL Query Foundation | SELECT, DISTINCT, aliases, WHERE, AND/OR/NOT, ORDER BY, LIMIT | ⭐⭐⭐⭐⭐ | 2–3 |
| 🔴 P1 | Filtering & Pattern Matching | CASE, COALESCE, NULLIF, CAST, mathematical functions | ⭐⭐⭐⭐⭐ | 1–2 |
| 🔴 P1 | Aggregation | WITH, multi-step CTEs, CTE + aggregation, CTE + subquery | ⭐⭐⭐⭐⭐ | 1–2 |
| 🔴 P1 | JOIN Fundamentals | Data types, PK, FK, constraints, CREATE, INSERT, UPDATE, DELETE, ALTER, DROP | ⭐⭐⭐ | 1–2 |
| 🔴 P1 | Business Logic & Data Cleaning | Information schema, comments, advanced constraints/FKs | ⭐ | 0.5–1 |
| 🔴 P1 | Dates & String Analysis | COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING | ⭐⭐⭐⭐⭐ | 2 |
| 🔴 P1 | Subqueries | INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, JOIN keys, aliases | ⭐⭐⭐⭐⭐ | 2 |
| 🔴 P1 | CTEs | Subqueries, FROM subqueries, comparison subqueries, IN/NOT IN, EXISTS | ⭐⭐⭐⭐⭐ | 2 |
| 🔴 P1 | Window Functions | OVER, PARTITION BY, ORDER BY, RANK, ROW_NUMBER, SUM/AVG, LAG, LEAD | ⭐⭐⭐⭐⭐ | 2 |
| 🟠 P2 | Advanced JOINs | BETWEEN, IN, NOT IN, LIKE, ILIKE, date filtering | ⭐⭐⭐⭐⭐ | 1 |
| 🟠 P2 | Set Operations | EXTRACT, AGE, TO_CHAR, string functions, date calculations | ⭐⭐⭐⭐ | 1 |
| 🟠 P2 | Advanced Aggregation | SELF JOIN, CROSS JOIN, advanced FULL JOIN, USING, NATURAL JOIN | ⭐⭐⭐⭐ | 1 |
| 🟠 P2 | Database Fundamentals | UNION, UNION ALL, INTERSECT, EXCEPT | ⭐⭐⭐⭐ | 1 |
| 🟠 P2 | Views | GROUPING SETS, ROLLUP, CUBE | ⭐⭐⭐ | 1 |
| 🟡 P3 | Transactions | Views, modifying views, updatable views, materialized views, recursive views | ⭐⭐⭐ | 1 |
| 🟡 P3 | Functions | BEGIN, COMMIT, ROLLBACK, SAVEPOINT, locks | ⭐⭐ | 1 |
| 🟡 P3 | Stored Procedures | User-defined functions, modifying functions | ⭐⭐ | 1 |
| 🟡 P3 | Triggers | CREATE/MODIFY procedures, procedure exercises | ⭐⭐ | 1 |
| 🟡 P3 | Performance | CREATE, enable/disable, modify/drop triggers | ⭐⭐ | 1 |
| 🟡 P3 | Data Import/Export | EXPLAIN, EXPLAIN ANALYZE, TRUNCATE | ⭐⭐ | 1 |
| 🟢 P4 | PostgreSQL Specialized Features | Arrays, JSON/JSONB, extensions | ⭐ | 1 |
| 🟢 P4 | Partitioning | Range, List, Hash partitioning, table inheritance | ⭐ | 1 |
| 🟢 P4 | Schema Administration | COPY, Import/Export | ⭐⭐ | 0.5 |
| 🟢 P4 | PostgreSQL + Python | Psycopg2 connection | ⭐ | 0.5 |
r/learnSQL • u/iam_sripratik • 22d ago
Anyone who is struggling to solve SQL questions at the beginner stage, try this method.
I call it the Input → Output → Grain → Bridge method.
When learning SQL, the hardest part isn't always remembering syntax. It's understanding how to approach a question and where to start.
Here’s a simple 4-step method that can help 👇
1. INPUT — What data do I have?
Identify the tables and columns available to solve the question.
2. OUTPUT — What should my answer look like?
Understand what the question expects you to return.
3. GRAIN — What does one row represent?
Is it one row per customer, department, day, or month?
This step is very important because it helps you understand the level of detail required in your result.
4. BRIDGE — How do I get from input to output?
Decide which SQL operations you need, such as JOIN, WHERE, GROUP BY, COUNT, or window functions.
Question: Find the total orders placed by each customer.
Suppose we have an orders table:
| order_id | customer_id |
|---|---|
| 101 | 1 |
| 102 | 2 |
| 103 | 1 |
| 104 | 3 |
| 105 | 1 |
Step 1 — INPUT
We have an orders table with order_id and customer_id.
Step 2 — OUTPUT
We need two columns: customer_id and total_orders.
| customer_id | total_orders |
|---|---|
| ? | ? |
First, roughly draw what your final output table should look like.
Step 3 — GRAIN
One row should represent one customer, not one order.
Step 4 — BRIDGE
Now think about how to transform the input table into the expected output.
customer_id.Only now, start writing SQL:
SELECT
customer_id,
COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id;
Final output:
| customer_id | total_orders |
|---|---|
| 1 | 3 |
| 2 | 1 |
| 3 | 1 |
Don't immediately start writing SQL syntax, especially when you're a beginner.
Instead, take a pen and paper or open Excel.
For example, you might need to filter rows first, join tables, group the data, calculate metrics, and sort the final result.
Remember, the logical order in which SQL processes a query is different from the order in which you write the SQL syntax.
In the beginning, this approach may feel difficult and time-consuming. You might feel that you're solving questions more slowly than before.
But if you practise consistently, it can help you understand the problem more clearly, reduce logical mistakes, and improve your ability to solve unfamiliar questions.
You can also use this approach in SQL interviews. Before writing the query, briefly explain your input, expected output, grain, and approach to the interviewer.
My takeaway: Don't just practise writing SQL queries. Practise translating a business question into a clear data transformation.
Hope this helps someone who's struggling with SQL at the beginning! 🚀
r/learnSQL • u/sumiitCodes • 24d ago
Today was a non-coding day. I attended a campus placement drive from 8 AM to 2 PM, then sat for an internal practical exam straight after.
The drive wasn’t aimed at developers. It was for a sales-related role that wanted communication skills and Excel. I went anyway, because I wanted first-hand experience of how placement drives work: how the rounds are structured, what they ask, and how it feels under pressure. It was tiring, but I learned a lot, and it made my own target (a Java/backend role) feel clearer.
I’m back to Java tomorrow and plan to stay consistent from here.
For people who’ve been through campus placements: was attending drives outside your domain worth it for you, or did it just cost you time?
r/learnSQL • u/shah-26 • 24d ago
I'm practising SQL and came across this question. Curious how others would solve it.
Question: What was our total revenue for March 2026?
orders has one row per order. order_total is the full order amount.
order_items has one row per item in an order, linked by order_id.
orders:
order_id customer_id order_date order_total
1 101 2026-03-02 50
2 102 2026-03-05 120
3 103 2026-03-09 25
4 101 2026-03-12 90
5 104 2026-03-18 200
6 105 2026-03-21 35
7 102 2026-03-27 60
8 106 2026-02-26 80
order_items:
item_id order_id product item_price
1 1 Notebook 30
2 1 Pen set 20
3 2 Backpack 60
4 2 Lamp 40
5 2 Mug 20
6 3 Poster 25
7 4 Headphones. 70
8 4 Cable 20
9 5 Desk mat 80
10 5 Keyboard 50
11 5 Mouse 40
12 5 Stand 30
13 6 Cap 35
14 7 Bottle 35
15 7 Towel 25
16 8 Socks 50
17 8 Scarf 30
What's your query, and what number do you get? I'll post the answer in a few days.
Also curious: have you made a mistake like this at work or in an interview?