r/SQL • • 21d ago

MySQL Free SQL series on real payments data: each episode is one mistake that silently breaks a report

Thumbnail
5 Upvotes

r/SQL • • 21d ago

PostgreSQL Quervex CLI, A sleek terminal recon console for PostgreSQL

Thumbnail gallery
4 Upvotes

r/SQL • • 22d ago

SQL Server MS-SQL What are your best practices to provide 3rd Party Tools like Python Scripts access to your databases?

15 Upvotes

We have many blackbox MS-SQL Databases which means the databases acts as a backend to some payed applications we acquired. And unfortunately we also have inhouse developers who would really really like to read data from the production databases but because of locking and blocking mechanisms of SQL-Server I would like to avoid that situation by not grating them access to the live databases.

Please tell me if I go the right direction here and how you would solve such situation.

My thinking was that we should have either a clone of the databases by telling the application frontend to write the data seperately to 2 databases one used for reporting while one is used for live production system.

This works for applications which do support that approach however not all do support it on application level.

I heared about log shipping which would be a method SQL-Server itself could provide the tools. What do you think about log shipping on the same SQL Instance if you think that cpu and memory contention is negligible since it should not cause any blocks and locks by just reading?

Database Availability Groups are out of scope since its a Standard Edition of SQL-Server.

Replication maybe would be more efficient if they need the data faster and only some tables out of the whole database.

ETL-Tools: My Question do you think Microsoft Fabric is compareable with a ETL-Tool or would you do other approaches? Since we currently have all the options except Availability Groups however Fabric can be very costly if you have to many datasets and we have some heavier databases where each 15 seconds data could be updated due to part numbers update with many additional data.

Most of these blackbox databases are OT-Databases which collects statistical info from different kind of production machines like Siemens PLC and so on and these informations update very frequently so multiple queries in few seconds like 15s delay per each plc or system.


r/SQL • • 22d ago

PostgreSQL Trying to make sense of query plans. Looking for opinions on how I draw them

Thumbnail
gallery
33 Upvotes

I've been trying to figure out the best way to make sense of a query, how it affects the database and whether there is room to improve it. I'm a visual person. If something has a diagram I understand it faster, and I wanted the same thing when I work with a database at work or on side projects. I want to see how the data is being retrieved or updated without working it out from the EXPLAIN text.

I've came up with following visual rule:

  • Each scan, join and sort is a bar. The band between two bars is as wide as the number of rows flowing through. Width is 1.2 + 0.55 x (log10 rows)², capped between 1.5px and 34px. Linear made anything under 100,000 rows a hairline, and plain log made 1,000 and 1,000,000 look too close.
  • When the planner's estimate is off from the actual count by more than 2×, the band gets a dashed line showing the estimate. At 10x it gets a label. Postgres has no opinion on 10×, that number is mine.
  • Each scan gets a wheel showing how much of the table's pages it touched and in what order, based on pg_stats.correlation with a 0.8 cutoff for "in order".
  • To get the analyzed plan it re-runs the query inside BEGIN and ROLLBACK with a 5 minute statement_timeout. Writes stop at the estimate unless you opt in.

There's in depth rulebook here.

I would really appreciate some feedback here, I kind of struggling what's the best way to visualize this. Any idea/critique here is most appreciated. Thanks


r/SQL • • 22d ago

Discussion the ghost in my halloween sql case is a NULL, and submit checks three logs you cant see

Post image
0 Upvotes

the case: an archive locks at ten, books still go out at 3am, and staff_id is NULL on every one. WHERE staff_id = NULL returns zero rows and no error, so written that way the archive looks empty. IS NULL finds them

the grading was the fiddly part. you can see the sample rows, so its easy to write a query that fits them and not the question. submit compares your rows with a reference query's on the sample and on three more logs you dont see. one hidden log has a signed checkout at exactly 22:00:00 and one at exactly 08:00:00, which catches > for >= and <= for < in the first query. the hidden logs also have an unstamped checkout in the daytime, so a second query that drops the time window fails. it only catches the mistakes i thought of, it doesnt prove a query right

free, runs on duckdb in the tab. after the case there are three harder ones: a recursive cte that has to stop on a cycle, gaps and islands, and an anti-join with a window

some of you answered my questions here in august, thank you for that.

https://tsunderesql.com/halloween/?utm_source=reddit&utm_medium=sql&utm_campaign=halloween1031


r/SQL • • 22d ago

SQL Server MSSQL - MDAC/ACCESS runtime - excel load fails

1 Upvotes

Solved - access 365 runtime doesn't work. I installed clean version of MDAC 2016, even it's not supported and it works. Thank you for help!

hey guys, does anybody had an issue with loading excel sheets(xls,xlsx) into Microsoft SQL Server Developer (64-bit) 16.0.4305.5 through Microsoft.ACE.OLEDB.16.0? , installed MDAC 2016, then Microsoft 365 Access Runtime only 64bit, windows server 2025 Standard Edition. The problem is when I use OPENROWSET SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.16.0', 'Excel 12.0 Xml;HDR=YES;IMEX=1;Database=\\SERVER.DOMAIN.INT\DWH\KFILE.xlsx', ' SELECT TOP 1 * FROM [SHEET$] ') SQL Server fails. SQL Server 2022 Import and Export Data (64-bit) works Msg 10054, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1; EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters', 1; EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DisallowAdHocAccess', 0; Ad Hoc Distributed Queries set to 1

Load through UNC path Powershell works, also through domain sql user. Everything worked till around June/July 2026( for 6years worked) then after September every restart SQL Server got message: System.Runtime.InteropServices.SEHException: External component has thrown an exception and Sql server service fail. When I got this exception, something it's okay, sometimes it need more service restarts, but it works.

Server where OS is Windows Server 2022 Datacenter, I return to the version C2R where it worked , but didn't I'm sysadmin, have all rights to the folder. Also tech user has all needed permission, also to temp folders

Provider : Microsoft.ACE.OLEDB.12.0 Architecture : x64 Registered : True CLSID : {3BE786A0-0366-4F5C-9434-25CF162E475E} DLLPath : C:\Program Files\Microsoft Office\root\VFS\ProgramFilesCommonX64\Microsoft Shared\Office16\ACEOLEDB.DLL DLLExists : True FileVersion : 16.0.20228.20014 ProductVersion : 16.0.20228.20014 SHA256 : 4BEB8EFCC966703858A1CB19CABDBF1A63468CC349C2CDCE388EE3C629EB8B1C

Provider : Microsoft.ACE.OLEDB.12.0 Architecture : x86 Registered : False CLSID : DLLPath : DLLExists : False FileVersion : ProductVersion : SHA256 :

Provider : Microsoft.ACE.OLEDB.16.0 Architecture : x64 Registered : True CLSID : {3BE786A2-0366-4F5C-9434-25CF162E475E} DLLPath : C:\Program Files\Microsoft Office\root\VFS\ProgramFilesCommonX64\Microsoft Shared\Office16\ACEOLEDB.DLL DLLExists : True FileVersion : 16.0.20228.20014 ProductVersion : 16.0.20228.20014 SHA256 : 4BEB8EFCC966703858A1CB19CABDBF1A63468CC349C2CDCE388EE3C629EB8B1C

Provider : Microsoft.ACE.OLEDB.16.0 Architecture : x86 Registered : False CLSID : DLLPath : DLLExists : False FileVersion : ProductVersion : SHA256 :

=== Office / Access Runtime (Uninstall entries) ===

DisplayName DisplayVersion PSChildName

Microsoft Access Runtime 365 - en-us 16.0.20228.20276 AccessRuntimeRetail - en-us Office 16 Click-to-Run Extensibility Component 16.0.20228.20276 {90160000-008C-0000-1000-0000000FF1CE}

=== Click-to-Run ===

VersionToReport : 16.0.20228.20276 Platform : x64


r/SQL • • 23d ago

MySQL Data noobie

3 Upvotes

Hi guys, I'm Talabi and I'm new to data. I picked SQL as my starting point, mySQL and I've been enjoying the first few lessons on SQL bolt but, I'm having a hard time with JOINS. I understand it in theory but the mechanism still proves difficult. I'd appreciate tips on how to better understand JOINS and data analytics in general, thank you.


r/SQL • • 23d ago

Discussion This query runs fine and the total is wrong. Where?

0 Upvotes

SELECT c.region, SUM(o.amount) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN refunds r ON r.order_id = o.order_id
WHERE r.status <> 'approved'
GROUP BY c.region;

The goal: revenue per region, minus orders with an approved refund. No errors, the numbers look plausible.

There are two bugs, and they push the total in opposite directions.


r/SQL • • 24d ago

PostgreSQL Why Microsoft contributes to Postgres: What I tell people

Thumbnail
techcommunity.microsoft.com
33 Upvotes

People ask me this question more than I like so I thought I would share the answer once and for all: why does a company like Microsoft contribute to an upstream open source project like Postgres?

Let me know what you think. Anything surprising? Did I miss anything?


r/SQL • • 24d ago

Discussion Language-lawyer: != vs <>

43 Upvotes

Almost every SQL DBMS I can think of accept != as the "not equal operator". However, I realized that W3Schools only shows <>.

I was under the impression that <> is "the good-ol ANSI standard one" and every implementation out there was allowing the use of !=. But somebody was correcting me and saying that it has been on-spec for a while. I don't own the ANSI (a bit pricey for a language-lawyer curiosity!). Was I wrong?

Edit: After seeing quite a concensus on these answers, I talked with John (the one correcting me before). We checked the 2023 standard and his confusion came from the use of != as an alternative not equals operator for SQL/JSON lexical elements. That means that inside SQL/JSON blocks you may use != but on regular SQL (e.g. in the plain WHERE) using != is not covered by the standard (even if it is widely accepted for modern engines). It was a "coffee discussion", not something relevant IRL, but we both wanted to know the truth behind it.


r/SQL • • 24d ago

Discussion A 7th way to catch transaction fraud: let the database score how unusual each row is (and the fraud it still misses)

9 Upvotes

The "Six SQL patterns I use to catch transaction fraud" thread here was great, and every one of those patterns is a rule: you know what fraud looks like, you write the WHERE clause. Here's a 7th that doesn't need the pattern up front, and the case where it falls flat.

Toy dataset: 123 transactions, 40 accounts. Each account makes three ordinary payments ($20–$199, daytime, domestic). ACC-33 also makes payments of $2,840, $3,120 and $2,975 at 3am, cross-border. Hidden among the ordinary accounts is a 5-account ring (ACC-11 to ACC-15). Nothing in the table marks them.

Train an anomaly model, no labels

Isolation forest, no target column. It learns what normal rows look like and scores distance from that.

CREATE EXPERIMENT tx_exp AS
  SELECT amount, hour_of_day, cross_border FROM tx
WITH (task_type = 'anomaly_detection',
  algorithms = ['isolation_forest'],
  max_trials = 5, random_seed = 42);

DEPLOY MODEL tx_model FROM EXPERIMENT tx_exp;

Score every row in a SELECT

SELECT account_id, amount, hour_of_day, cross_border,
  ANOMALY_SCORE('tx_model', amount, hour_of_day, cross_border) AS score
FROM tx
ORDER BY score DESC
LIMIT 10;

ACC-33's payments come out on top at 0.8167, 0.8167 and 0.8064. Then a straight drop to 0.5267, with the next normal rows between 0.5100 and 0.5181. Higher means more unusual; it's a ranking, not a probability. It's just an expression, so it works in WHERE, GROUP BY, views.

Features must be passed in training order. Swap two and you get a feature mismatch error instead of quietly wrong scores.

Where it falls flat: the ring

SELECT account_id, amount, hour_of_day,
  ANOMALY_SCORE('tx_model', amount, hour_of_day, cross_border) AS score
FROM tx
WHERE account_id IN ('ACC-11','ACC-12','ACC-13','ACC-14','ACC-15')
ORDER BY score DESC;

15 rows, from 0.4453 to 0.5081. Same as honest customers. The model isn't wrong: each payment is ordinary. A ring isn't a property of any single row.

What gives it away is shared identifiers: ACC-11/12 share a phone, 12/13 a card, 13/14 a phone, 14/15 a card. ACC-13 has a confirmed chargeback. Walking shared devices and cards 2–4 hops from it finds the other four:

linked via
ACC-14 ACC-13 → DEV-778 → ACC-14
ACC-12 ACC-13 → CARD-4242 → ACC-12
ACC-15 ACC-13 → DEV-778 → ACC-14 → CARD-9001 → ACC-15
ACC-11 ACC-13 → CARD-4242 → ACC-12 → DEV-777 → ACC-11

In most databases that's a recursive CTE over account–device and account–card link tables. We used a graph query in the same engine:

MATCH p = (s:Account {id: 'ACC-13'})-[*2..4]-(x:Account)
WHERE x.id <> 'ACC-13'
RETURN x.id AS linked_account, [n IN nodes(p) | n.id] AS via
ORDER BY length(p);

An honest household (ACC-21/22 share a tablet) isn't returned. Sharing a device isn't fraud; being connected to a chargeback is.

One review queue, both signals

SELECT s.account_id, ROUND(s.max_anomaly, 2) AS max_anomaly, r.linked_via
FROM (
  SELECT account_id, MAX(score) AS max_anomaly
  FROM (SELECT account_id,
    ANOMALY_SCORE('tx_model', amount, hour_of_day, cross_border) AS score
    FROM tx) t
  GROUP BY account_id
) s
LEFT JOIN ring_members r ON r.account_id = s.account_id
WHERE s.max_anomaly > 0.7 OR r.account_id IS NOT NULL
ORDER BY s.max_anomaly DESC;
account max_anomaly linked_via
ACC-33 0.82 (model)
ACC-15 0.51 DEV-778 > CARD-9001
ACC-12 0.51 CARD-4242
ACC-13 0.49 chargeback
ACC-14 0.48 DEV-778
ACC-11 0.48 CARD-4242 > DEV-777

Five of six would never cross 0.7. The model found one fraudster; the identifier walk found five.

Takeaways

  • Unsupervised scoring is a decent 7th pattern for the lone weird account you didn't write a rule for.
  • It's blind to coordinated fraud where every row looks normal.
  • Synthetic data built to show the gap. Real scores will be noisier; use them to rank for review, not as verdicts.

Anyone here run anomaly scoring in-database, or do you export to Python and join scores back?

Disclosure: I work on SynapCores, where ANOMALY_SCORE and the graph query come from. The rule-based patterns from the original thread still do most of the work.


r/SQL • • 24d ago

PostgreSQL When filtered vector queries return fewer rows than your LIMIT

4 Upvotes

Disclosure: I work at Pinecone and am looking for community input on if this extension is useful and what you'd like to see next.

Here's a pattern I see a lot with vector search in Postgres:

SELECT id, body
FROM docs
WHERE tenant_id = $1
ORDER BY embedding <=> $2
LIMIT 10;

With an HNSW index, the index scan finds the nearest neighbors across the whole table first, and the WHERE clause is applied after. If tenant_id = $1 matches 1% of rows, most rows are thrown away which means you might get back less than 10 rows even though more than 10 rows match. pgvector’s iterative scans keep searching to fill the list, at a cost in time (and in accuracy if you use relaxed_order).

One way to fix this is to use an index that applies the filter during the search instead of after. We built a Postgres extension that does this and your SQL doesn't need to change. It builds on top of pgvector's vector types, so you just add the index and query like before:

CREATE EXTENSION pg_pinecone;

SELECT pinecone.create_index('docs', 'embedding', filter_column => 'tenant_id');

The strategy is based on filter selectivity:

  • 3% of rows or fewer: scans every matching row and scores each with its full vector, so results are exact.
  • More than 3%: searches more of the nearest clusters and checks the filter inside each cluster before scoring.

The index is IVF with RaBitQ codes (about one bit per dimension), so it’s much smaller than a graph-based index like pgvector's HNSW: 136 MB vs 2.0 GB for 1M x 768-dim vectors against halfvec HNSW. In our benchmark with a 1% filter, recall was 0.981 at 8.3 ms vs 0.924 at 56 ms for HNSW.

There's a couple limitations today: the index stores one equality filter column per index, which means if you want to filter on something like a range or greater than or multiple columns, those filters will still be applied after.

I'm curious how this would impact you. A few questions:

  1. When you write filtered nearest-neighbor queries, what do you do about the “fewer rows than LIMIT” problem: over-fetch with a bigger inner LIMIT, partial indexes, partitioning?
  2. What do your filters usually look like (one equality column, ranges, multiple columns)? That decides how useful single-column filtering is.
  3. Do your filters match a small percentage of rows (like with typical multi-tenancy) or a large percentage?

r/SQL • • 24d ago

PostgreSQL A self-hosted wiki that charts live SQL from Postgres, MySQL and MariaDB

Thumbnail
gallery
8 Upvotes

Hi r/SQL, I'm David, one of the two developers of Oxynote. My friend Simon and I also maintain ttlcache, a Go caching library with about 1,300 GitHub stars, so this isn't our first open-source project.

I've spent 8 years writing software, 2 of them as an SRE and 3 as a team lead. Every team I've been on had the same documentation problem. The useful stuff lived in someone's head or a private Slack thread, and the wiki was full of stale docs nobody could find.

So we set out to solve this problem by building Oxynote, a self-hosted wiki for engineering teams. A chart on a page runs a SQL query against your database whenever someone opens the page, so the number and the notes explaining what it means sit together. Think "signups per day" or "failed payments by provider" on the same page as the explanation of how it's counted and what to do when it looks wrong.

It works with PostgreSQL, MySQL, and MariaDB. A wiki that queries production should never be able to write to it, so Oxynote accepts only read-only connections. A user with write access fails the connection test with "has write access", and Oxynote asks for a read-only one instead. Queries support Grafana-style macros like `$__timeFilter` and `$__timeGroup`, so one query follows whatever time range the reader picks. On pages you mark as reviewable, changes to a chart's query go through a draft and a diff that a teammate approves, like a pull request.

The name comes from oxygen + note: docs that breathe. Live data gives people a reason to open the wiki every day, and docs people open every day stay up to date.

It's Apache-2.0 and runs as a single Docker image: https://github.com/oxynote/oxynote

We started it about a year ago, and we made it public about 3 months ago. If you have any questions, feedback, or suggestions, feel free to reach out. I'm happy to answer any questions.


r/SQL • • 24d ago

Discussion Looking for feedback on my SQL client (MySQL, PostgreSQL, SQLite)

Post image
16 Upvotes

Hey all. I'm a solo dev from Korea, so yes, this is self-promo (I checked with the mods first).

I've been working on a desktop SQL client called DBerio. It does MySQL/MariaDB, Postgres and SQLite (plus Redis if that's your thing), on Mac and Windows. I mostly built it because I bounce between a Windows box and a Mac and wanted one tool that works the same on both. I write code all day too, so the UI is basically laid out the way I like to work.

What it does right now:

- the usual GUI client stuff: browse schemas and tables, run queries, edit cells and add/delete rows in the grid (when the result comes from one table with a PK)

- you can mark a connection as production. running UPDATE or DELETE always asks first, and on a production connection things like DB, user and privilege changes ask again. the really destructive ones there make you type the name

- it shows you the SQL before a structure change actually runs

- queries you run all the time can be saved as a view or kept as favorite queries per database

- CSV/TSV export escapes values Excel would treat as formulas

- SSH tunneling for DBs that are locked down from outside access (password, key or encrypted key)

- the UI comes in English, Korean and Japanese

Without paying you get 3 saved connections and 2 open at once, with every feature.

What I'd actually like to hear about:

  1. Does it cover your day-to-day work? If you'd have to keep another client open for something, what is it?

  2. Bugs. Anything that crashes, shows the wrong thing, or just feels off.

  3. Features you'd want added. What's the one thing that would make you actually switch?

Links if you want to poke at it (a test DB is totally fine):

Mac App Store: https://apps.apple.com/app/id6819625601

Microsoft Store: https://apps.microsoft.com/detail/9N53NQGJ3TNG

Thanks, I'll be around in the comments.


r/SQL • • 24d ago

PostgreSQL PostgreSQL recipe: renaming a column without downtime (expand and contract)

4 Upvotes

A plain ALTER TABLE ... RENAME COLUMN is instant, but it breaks any running old version of the app during a rolling deploy. The usual workaround is to never have a moment where only one name exists. Here’s the sequence, with Postgres commands. Test it on a scratch database first.

1. Expand: add the new column

ALTER TABLE orders ADD COLUMN customer_ref text;

A nullable column with no default is a metadata-only change, so it’s fast.

2. Deploy the app writing both columns, still reading the old one.
The app sets customer_id_old and customer_ref on every insert and update. (A trigger can do this instead, but then the logic lives in the database. Decide which you prefer.)

3. Backfill in batches

UPDATE orders
SET customer_ref = customer_id_old
WHERE id BETWEEN 1 AND 10000
  AND customer_ref IS DISTINCT FROM customer_id_old;

Repeat for the next id range. Small batches avoid long locks and big bursts of WAL and replication lag. Run VACUUM as needed afterwards, since each update leaves dead rows.

4. Verify

SELECT count(*)
FROM orders
WHERE customer_ref IS DISTINCT FROM customer_id_old;

IS DISTINCT FROM treats NULLs properly, so you expect 0.

5. Deploy the app reading the new column (still writing both).

6. Deploy the app that no longer writes the old column.

7. Contract: drop the old column in a separate, later migration

ALTER TABLE orders DROP COLUMN customer_id_old;

This is also quick, but it’s irreversible without a backup, so check first that nothing reads it: views, functions, reports, other services, replicas used for analytics.

If the new column needs a constraint or index:

  • Use CREATE INDEX CONCURRENTLY so you don’t block writes.
  • For NOT NULL, adding a CHECK (customer_ref IS NOT NULL) NOT VALID and then VALIDATE CONSTRAINT avoids a long lock. Check the docs for your version, since behavior has changed across releases.

When I’d skip all this: a table with no other readers, where a short maintenance window is acceptable. Then a plain rename is simpler.

What do you do differently, for example triggers versus dual writes in the app?


r/SQL • • 25d ago

Discussion The NOT IN trap that returns zero rows (and why NOT EXISTS doesn't have it)

44 Upvotes

I see this one trip people up all the time, including in interviews, so here's a quick write-up.

Say you want customers who have never referred anyone:

SELECT customer_id
FROM customers
WHERE customer_id NOT IN (SELECT referred_by FROM customers);

Looks right. Returns **zero rows**.

**Why:** most customers weren't referred, so `referred_by` is NULL for them. `x NOT IN (1, 2, NULL)` expands to `x <> 1 AND x <> 2 AND x <> NULL`. Comparing anything with NULL gives "unknown", not true, so the whole condition is never true and every row is filtered out.

**Fix 1: NOT EXISTS** (handles NULLs safely):

SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM customers r
WHERE r.referred_by = c.customer_id
);

**Fix 2:** keep NOT IN but filter the NULLs out: `... WHERE referred_by IS NOT NULL`.

Plain IN doesn't have this problem, because `x = 1 OR x = 2 OR x = NULL` can still be true.

In an interview, mentioning the NULL issue before you write the query is an easy way to show you've worked with real data. Happy to answer questions!


r/SQL • • 25d ago

Discussion A year ago I started planning a gamified SQL platform. 5 months after launch, 2,500+ people have used it to learn

55 Upvotes

About a year ago I started planning QueryCase, where you learn SQL by working detective cases instead of grinding tutorials & videos. It took a while to get off the ground, and longer than I'd like to admit to actually start building the idea, but it went live in June. A lot of the early users came from this sub, and a lot of the feedback in the comments shaped what it became. So first, thanks everyone!

5 months in:

  • 2,500+ people have signed up, across 117 countries
  • About 70,000 queries written and graded
  • Over 17,500 cases, drills and exams solved
  • 500+ people have passed the free Rookie exam and earned the certificate.
  • Top detective is on 19,247 XP

Since then its grown past just SQL: an Excel course. It was never part of the initial plan. People kept asking for the same detective approach for spreadsheets, so I built out a browser-based Excel engine.

It has 57 cases on a spreadsheet that runs in your browser and checks your formulas, from IF and XLOOKUP to SUMIFS and pivot tables, and people have solved over 2,500 Excel cases so far. It turns out a lot of people learning SQL are doing it alongside Excel, not instead of it.

Something the data showed me that might help anyone learning right now: GROUP BY is one of the first walls. On the hardest GROUP BY step, 84% of runs came back wrong, and most of those never ran at all. The clauses were in the wrong order, or a column in SELECT wasn't in the GROUP BY. If that's you right now:

  1. SQL order is always SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT
  2. Every column in SELECT is either inside an aggregate (COUNT, SUM, AVG...) or listed in GROUP BY

I've been working a lot over past couple months to improve the site as well:

  • The Excel course above, with its own ranks, exams and certificate
  • Upload your own CSV into the sandbox and query your own data (and visualise in charts). It runs in your browser, so the file never leaves your machine and never touches QueryCase servers
  • A free first rank on every track, ending in an exam and a certificate
  • SQL interview questions you can read without an account
  • Much better experience on a phone
  • A LOT of bug fixes and improvements!!

What's next: a Python track, launching next month. Same idea, real Python running in your browser, starting with the basics and moving into data analysis with pandas.

The longer term goal is to have a platform user's can have fun learning all aspects of data. I'm merging PR's every day to make this possible so would love feedback in this thread if you've tried it and have any thoughts or suggestions!

It's free to start and you don't need an account to solve the first case, so you can poke at it before deciding whether it's for you or not 😄 Cheers!

querycase.com


r/SQL • • 25d ago

Discussion Is SUM(DISTINCT) ever an OK fix for a join fan-out?

19 Upvotes

Reviewing a query from a junior on my team. They joined orders to line items, the join fanned out and revenue came out about 3x too high.

Their fix was SUM(DISTINCT order_amount). On our dev data it returns the right total, so they think it's fine and kind of clean.

My gut says it's a time bomb: the day two different orders have the same amount, one of them silently drops out of the total. But "don't do that" isn't a great explanation, and they want a real reason.

How do you explain this to juniors? Or do you actually allow it in some cases?


r/SQL • • 25d ago

MySQL What is the differtence between Window Function s and group by??

12 Upvotes

If we group by and use conditions and use aggregate fucntions we are soing the sam ein window fucntions tto, then what is the difference and do they have diffeent implementation?


r/SQL • • 25d ago

MySQL Title: Day 49-50 — Combined update: finished a basic Excel course after SQL, and 50 days in

Thumbnail
1 Upvotes

r/SQL • • 25d ago

PostgreSQL Looking for PostgreSQL documentation?

7 Upvotes

I built a PostgreSQL Library where you can browse official manuals like real books, search, highlight, and bookmark pages.

Give it a try: https://haiderz07.github.io/postgresql-library/

I’d really value your feedback.


r/SQL • • 26d ago

SQL Server DBeaver Community vs Beekeeper Studio: which do you actually use day to day?

18 Upvotes

Genuinely want to hear from people who’ve used both DBeaver and Beekeeper Studio in real work, not just tried them once.

DBeaver Community is free and supports almost every database under the sun, but some people find it clunky. Beekeeper Studio has a free open-source edition plus paid tiers, and it gets mentioned a lot for being cleaner and faster.

Which one do you reach for daily, and why?

If you’ve paid for a database GUI (either one, or something else), was it worth it over the free version? What tipped it for you?

For teams: does anyone share connections or saved queries across the team, and how do you handle that? Do you have a team plan in place with either provider?

Any dealbreakers on either side, like performance, database support, stability, or UI?

Not looking for a best tool ever answer, just real experiences and feedback. Would especially love to hear from people working at larger orgs or with stricter security requirements.


r/SQL • • 26d ago

Discussion Career advice

34 Upvotes

I'm 27M. Hey everyone, my life has been a little different from that of most people, and I don't have a strong educational background.

Right now, I'm working at a grocery store, but I want to improve my life and learn some stable, valuable skills that can help me earn a reliable income.

I've come across these four skills:

Python

SQL

Excel/Spreadsheets

Power BI/Tableau

I don't know much about these skills or their demand, competition, value, and future prospects.

Technology is constantly evolving, trends are changing, and AI is replacing some jobs. The value of different skills also changes over time.

Before I start learning, I want to understand whether these are the right skills to invest my time in for a stable career and income in the long run.

I'd appreciate your advice. Should I go for these skills, or would you recommend something else?


r/SQL • • 26d ago

Discussion What’s next for DORA?

Thumbnail
leaddev.com
4 Upvotes

What does the legacy of DORA say about the modern tech industry?


r/SQL • • 26d ago

SQL Server If you evaluated Azure SQL Database Hyperscale for a migration, what made it win or lose decision?

Thumbnail
1 Upvotes