SQL Guide: How to Detect and Handle Duplicate Rows in Any Table

·1 views

Data analyst Michael Nocito published a practical SQL tutorial on August 7, 2026, explaining how to identify and manage duplicate rows in database tables. The guide uses a 14-row sample customers table with three intentionally seeded duplicates to demonstrate real, verifiable query results. Nocito emphasizes that defining what counts as a duplicate — whether rows must match on all columns or just one — is a critical decision that must be made before writing any query. The core method involves grouping rows by key columns, filtering for groups with a COUNT above one, and using ROW_NUMBER to flag extras rather than immediately deleting them. A quick diagnostic check comparing COUNT(*) against COUNT(DISTINCT key) is recommended as a first step whenever working with an unfamiliar table.

Read the full story at DEV Community

This is an AI-generated summary. ShortSingh links to the original source for the complete article.

Discussion (0)

How to Set Up a Free SQL Practice Database in Under Five Minutes

Data analyst Michael Nocito published a practical guide on August 8, 2026, explaining how beginners can set up a real SQL practice environment at no cost. The guide recommends downloading the Chinook database, a simulated digital music store containing 11 tables, 3,503 tracks, and 412 customer invoices in a single SQLite file. Users are instructed to open the file in DB Browser for SQLite and run a row-count query to verify all tables loaded correctly. Nocito argues that sample databases are superior to spreadsheets for learning SQL because they contain pre-linked tables, enabling practice with joins, grains, and reconciliation — skills commonly tested in data job interviews. Two other popular options, Northwind and Sakila, are also mentioned as alternatives depending on the learner's needs.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

How to Segment Customers in SQL Using CASE WHEN Expressions

Data analyst Michael Nocito published a technical tutorial on August 8, 2026, explaining how to perform customer segmentation in SQL using CASE WHEN logic. The guide walks through aggregating a transactions table down to one row per customer, then applying a CASE expression to assign each customer a named group based on metrics like spend, purchase frequency, and recency. A sample dataset of 12 customers, 19 purchases, and $2,430 in total spend loaded into DuckDB is used throughout to demonstrate and verify each query. Nocito emphasizes that the grain must be correct before labelling — meaning data should first be aggregated to the customer level, then grouped by the resulting segment name. The tutorial also covers validation techniques, such as confirming all customers land in exactly one segment and that no rows fall into an unintended null group.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Z.ai Launches GLM-5.3 with 32K Token Context and AI-Powered Security Scanner

Chinese startup Z.ai has released GLM-5.3, the latest version of its open-source coding model, initially available through its GLM Coding Plan and ZCode cloud IDE. The model supports up to 32,000 tokens per request, enabling developers to work across entire codebases in a single prompt with improved long-horizon reasoning. Z.ai trained the model on security advisories and code-review data, adding a vulnerability-aware layer that cross-references generated code against known exploit patterns. Shortly after launch, GLM-5.3's security scanner flagged a potentially serious vulnerability in Cursor, an AI-assisted IDE backed by SpaceX, involving possible unauthorized code execution via malicious extensions. Z.ai publicly disclosed the finding on X while awaiting confirmation from Cursor's team, raising broader questions about AI developers' responsibility in disclosing security flaws in third-party tools.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Developer Rebuilds Law Firm Document Pipeline in 200 Lines of Python, Cuts RAM Use by 97%

A developer automating his law firm's document workflows replaced a Node.js and Puppeteer-based pipeline with a Python rewrite after the original system crashed at 3 AM while processing 5,000 PDFs, consuming 6.2GB of RAM. The new system uses only Python's standard library and a ThreadPoolExecutor, reducing peak memory usage from 6.2GB to approximately 180MB. Key fixes included replacing an unbounded Redis queue with a bounded asyncio queue to enforce backpressure and prevent out-of-memory crashes. The developer also addressed a court API rate-limiting issue by implementing exponential backoff with jitter using pure asyncio, eliminating the need for external dependencies. The refactored stack processed 48,000 documents compared to 4,200 under the previous Node.js setup, while running within an 8GB instance constraint.

0 comments Read more at DEV Community