Daily Successful Transactions Per Department
Reported by candidates from Infosys's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The mistake that sinks a first attempt on this Infosys SQL question, reported in September 2026, is filtering on status in the WHERE clause. You write a clean GROUP BY, run it, and the zero-count rows vanish. The task asks for every date and every department, including pairs with no successful sales. That means a cross join of dates and departments, then a conditional count. It's a short query, but one wrong clause drops rows and fails hidden tests. If you blank mid-assessment, StealthCoder runs invisibly as a safety net and surfaces the structure while you type.
The problem
The table products stores one row per product, including that product's department. The table transactions stores one row per product sale attempt, including the transaction date, department, price, and status. For every distinct transaction date and every department that appears in products, count the successful transactions. A transaction is successful when its status is exactly successful. Return transaction_date, department_id, and successful_count. Include department-date pairs with zero successful transactions. Order rows by transaction_date ascending, then department_id ascending. What the interview report shared There is a products table with productId, deptId, and price, and a transaction table with productId, deptId, transactionDate, price, and status. Find, for every day, for each department, the count of successful transactions. productsColumnMeaning product_idUnique product identifier dept_idDepartment identifier priceListed product price transactionsColumnMeaning product_idProduct sold or attempted dept_idDepartment of the transaction transaction_dateCalendar date of the transaction priceTransaction price statusTransaction outcome text Tables products: product_id PK (Integer), dept_id (Integer), price (Integer) transactions: product_id (Integer), dept_id (Integer), transaction_date (Date), price (Integer), status (Text) Constraints Each table has between 1 and 10^5 rows. product_id is unique and non-null in products. dept_id, price, transaction_date, and status are non-null. A transaction is successful only when status is exactly successful.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is building the full grid first. Take SELECT DISTINCT transaction_date from transactions and CROSS JOIN it with SELECT DISTINCT dept_id from products. That gives every date-department pair. Then LEFT JOIN transactions on both date and dept_id, and count with SUM(CASE WHEN status = 'successful' THEN 1 ELSE 0 END) or COUNT(CASE WHEN... THEN 1 END). Put the status test inside the aggregate, never in WHERE, or the zeros disappear. A second pitfall is COUNT(*) after a left join, which counts the null-filled row as 1. Use a conditional count instead. Also match the status exactly, since the spec says exactly successful, so skip LOWER or LIKE. Finish with ORDER BY transaction_date, department_id. If the syntax slips under time pressure, StealthCoder is the hedge on the live OA, reading the prompt and giving you the query shape.
If this hits your live OA and you blank, StealthCoder solves it in seconds, invisible to the proctor.
You can drill Daily Successful Transactions Per Department cold, or you can hedge it. StealthCoder runs invisibly during screen share and surfaces a working solution in under 2 seconds. The proctor sees the IDE. They don't see what's behind it. Built by an Amazon engineer who would have shipped this the night before his JPMorgan OA if he'd had it.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass Infosys's OA.
Infosys reuses patterns across OAs. Built by an Amazon engineer who would have shipped this the night before his JPMorgan OA if he'd had it. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Daily Successful Transactions Per Department FAQ
How hard is the Infosys daily successful transactions question really?+
It's easy on logic and unforgiving on detail. There's no complex algorithm. The whole difficulty is remembering that zero-count department-date pairs must appear, which forces a cross join plus left join instead of a plain GROUP BY.
What's the trick to this query?+
Generate every date and department pair first with a cross join of distinct dates and distinct departments. Then left join transactions and count successes with a CASE expression. The status filter lives inside the aggregate, not in WHERE.
Why do my zero rows keep disappearing?+
You're filtering status in WHERE, which removes non-matching rows before grouping. A pair with no successful sale then has no rows left to group. Move the condition into SUM(CASE WHEN...) so the pair survives with a zero.
Should I use COUNT(*) or a conditional count?+
Use a conditional count. After a left join, COUNT(*) counts unmatched pairs as 1 because the null-filled row still exists. COUNT(CASE WHEN status = 'successful' THEN 1 END) or SUM with CASE returns the correct zero.
How do I prepare for this in 48 hours?+
Practice three things: cross join to build a grid, left join with the join condition on multiple columns, and conditional aggregation. Write this exact query twice from scratch, then check ordering by date and department ascending. That covers nearly everything it tests.