--- title: SQL Interview Question | Can You Solve This Inventory... description: A walkthrough of a SQL interview problem called 'First Stockout' (problem 1326 on SQL Nest), commonly asked at major tech companies. The solution involves... canonical: https://daily.dev/posts/sql-interview-question-can-you-solve-this-inventory-stockout-problem--7bwkzm5xr twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: SQL Interview Question | Can You Solve This Inventory Stockout Problem? | daily.dev og:description: A walkthrough of a SQL interview problem called 'First Stockout' (problem 1326 on SQL Nest), commonly asked at major tech companies. The solution involves... og:url: https://daily.dev/posts/sql-interview-question-can-you-solve-this-inventory-stockout-problem--7bwkzm5xr og:image: https://api.daily.dev/og/posts/7BwKZm5xr.png og:image:alt: SQL Interview Question | Can You Solve This Inventory Stockout Problem? og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # SQL Interview Question | Can You Solve This Inventory Stockout Problem? **[techTFQ](https://daily.dev/sources/techtfq)** · 28 min read · 1 upvotes · 0 comments ## Summary A walkthrough of a SQL interview problem called 'First Stockout' (problem 1326 on SQL Nest), commonly asked at major tech companies. The solution involves calculating a running inventory balance using a SUM window function with a CASE statement to handle 'in' vs 'out' transactions, then using CTEs and ROW_NUMBER to identify the first date each product's balance dropped to zero or below. The final query joins multiple CTEs to return product ID, name, first restock date, first stockout date, balance at stockout, and days between first stock and first stockout, sorted by days to stockout ascending. ## Full article daily.dev links to this article rather than hosting it. Read it at the original source: ## Questions this post answers ### How do I calculate a running inventory balance in SQL where 'in' transactions add and 'out' transactions subtract? Use SUM as a window function with a CASE statement inside it: SUM(CASE WHEN transaction_type = 'in' THEN quantity ELSE -quantity END) OVER (PARTITION BY product_id ORDER BY transaction_id). Partitioning by product keeps the running total separate per product, and negating the quantity for 'out' transactions turns the cumulative sum into a subtraction whenever stock leaves the warehouse. _Practicing running-balance patterns like this gets easier with daily.dev's stream of SQL problem walkthroughs and window-function explainers._ ### How do I find only the first occurrence of an event per group when it happened multiple times in SQL? Apply ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY transaction_id) to rank each qualifying event chronologically within its group, then filter the outer query to row number equal to 1. This isolates the earliest occurrence per product even when the same condition, such as an inventory dropping to zero, is met multiple times. _Developers refining row-numbering and deduplication tricks in SQL can keep discovering technique breakdowns like this on daily.dev._ ## Similar posts on daily.dev - [10 Simple Steps to Solve SQL Problems \[2026\]](https://daily.dev/posts/10-simple-steps-to-solve-sql-problems-2026--uicxpyy02) · Planet MySQL · 0 upvotes · 0 comments - [Day 160 of Learning Java & DSA: Solving the Stock Span Problem Using Stack](https://daily.dev/posts/day-160-of-learning-java-dsa-solving-the-stock-span-problem-using-stack-cygwwuyry) · Medium · 2 upvotes · 0 comments - [Solving the "Impossible" in ClickHouse: Advent of Code 2025](https://daily.dev/posts/solving-the-impossible-in-clickhouse-advent-of-code-2025-wgtl9owtg) · ClickHouse · 2 upvotes · 0 comments --- Tags: [#backend](https://daily.dev/tags/backend), [#sql](https://daily.dev/tags/sql) [View this post on daily.dev](https://daily.dev/posts/sql-interview-question-can-you-solve-this-inventory-stockout-problem--7bwkzm5xr) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"TechArticle","headline":"SQL Interview Question | Can You Solve This Inventory Stockout Problem?","url":"https://daily.dev/posts/sql-interview-question-can-you-solve-this-inventory-stockout-problem--7bwkzm5xr","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/sql-interview-question-can-you-solve-this-inventory-stockout-problem--7bwkzm5xr"},"datePublished":"2026-07-31T11:38:37.569Z","dateModified":"2026-09-14T06:04:03.642Z","description":"A walkthrough of a SQL interview problem called 'First Stockout' (problem 1326 on SQL Nest), commonly asked at major tech companies. The solution involves...","image":"https://i.ytimg.com/vi/SLc6L3bxJYs/sddefault.jpg","thumbnailUrl":"https://i.ytimg.com/vi/SLc6L3bxJYs/sddefault.jpg","isAccessibleForFree":true,"articleSection":"techTFQ","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"techTFQ","logo":"https://media.daily.dev/image/upload/s--dgjhXj89--/f_auto,q_auto/v1780213385/logos/techtfq?_a=BAMAMiWQ0","url":"https://daily.dev/sources/techtfq"},"commentCount":0,"discussionUrl":"https://daily.dev/posts/sql-interview-question-can-you-solve-this-inventory-stockout-problem--7bwkzm5xr","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":1},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"keywords":"backend,sql","timeRequired":"PT28M","video":{"@type":"VideoObject","name":"SQL Interview Question | Can You Solve This Inventory Stockout Problem?","description":"A walkthrough of a SQL interview problem called 'First Stockout' (problem 1326 on SQL Nest), commonly asked at major tech companies. The solution involves...","thumbnailUrl":"https://i.ytimg.com/vi/SLc6L3bxJYs/sddefault.jpg","uploadDate":"2026-07-31T11:38:37.569Z","duration":"PT28M","url":"https://api.daily.dev/r/7BwKZm5xr","embedUrl":"https://www.youtube.com/embed/SLc6L3bxJYs"}} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"techTFQ","item":"https://daily.dev/sources/techtfq"},{"@type":"ListItem","position":3,"name":"SQL Interview Question | Can You Solve This Inventory Stockout Problem?"}]} {"@context":"https://schema.org","@type":"FAQPage","@id":"https://daily.dev/posts/sql-interview-question-can-you-solve-this-inventory-stockout-problem--7bwkzm5xr#faq","mainEntity":[{"@type":"Question","name":"How do I calculate a running inventory balance in SQL where 'in' transactions add and 'out' transactions subtract?","acceptedAnswer":{"@type":"Answer","text":"Use SUM as a window function with a CASE statement inside it: SUM(CASE WHEN transaction_type = 'in' THEN quantity ELSE -quantity END) OVER (PARTITION BY product_id ORDER BY transaction_id). Partitioning by product keeps the running total separate per product, and negating the quantity for 'out' transactions turns the cumulative sum into a subtraction whenever stock leaves the warehouse. Practicing running-balance patterns like this gets easier with daily.dev's stream of SQL problem walkthroughs and window-function explainers."}},{"@type":"Question","name":"How do I find only the first occurrence of an event per group when it happened multiple times in SQL?","acceptedAnswer":{"@type":"Answer","text":"Apply ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY transaction_id) to rank each qualifying event chronologically within its group, then filter the outer query to row number equal to 1. This isolates the earliest occurrence per product even when the same condition, such as an inventory dropping to zero, is met multiple times. Developers refining row-numbering and deduplication tricks in SQL can keep discovering technique breakdowns like this on daily.dev."}}]} ```