easy NULL Handling E-Commerce
Revenue Lower Than Expected The monthly revenue report shows $180K, but the sales team manually counted $210K from their deal tracker. The query is simple: SELECT SUM(total_amount) FROM orders. Where is the missing $30K?
Tables: orders
easy Referential Integrity E-Commerce
Orphan Records Breaking Reports The customer success team runs a report of all orders with customer details. But 15% of orders are missing from the report. The query uses INNER JOIN between orders and customers. No errors are thrown.
Tables: orders, customers
easy Invalid Values Finance
Negative Account Balances The compliance team flagged that some customer accounts show negative balances, which should be impossible for savings accounts. The risk dashboard needs this fixed ASAP.
Tables: accounts, customers
easy Duplicate Data Finance
Duplicate Customer Emails The CRM team is sending marketing emails but some customers receive duplicate emails. The team suspects there are duplicate email entries in the customers table.
Tables: customers
easy Missing Data Healthcare
Patients Missing Phone Numbers The appointment reminder system is failing to reach 30% of patients. The system sends SMS reminders but can't find phone numbers for many patient records.
Tables: patients
easy Invalid Values E-Commerce
Products with Zero Price The pricing team noticed that some products are showing as free on the website. These are not promotional items — they should have prices but show $0.00.
Tables: products, categories
easy Stale Data Healthcare
Unpaid Overdue Bills The billing department says they have no overdue invoices, but the accounts receivable report shows $50K outstanding. The billing query only looks at status = 'pending'.
Tables: billing, patients
easy Referential Integrity E-Commerce
Missing Order Items The warehouse team says some orders have no items to pick. The orders exist in the system but when they look up the line items, nothing shows up.
Tables: orders, order_items
easy Stale Data Finance
Expired Active Cards The fraud team noticed that some credit cards marked as 'active' have expiration dates in the past. These should have been deactivated automatically.
Tables: cards, accounts
easy Missing Data Healthcare
Visits Without Doctor Assignment The patient satisfaction survey system can't send surveys for 20% of visits because the doctor field is empty. The system needs a doctor name to personalize the survey.
Tables: visits, doctors
medium Pipeline Gap E-Commerce
Yesterday's Orders Are Missing The operations team notices the daily orders dashboard shows zero orders for yesterday, but the warehouse shipped 150 packages. The ETL pipeline runs at midnight UTC.
Tables: orders, customers
medium Duplicate Counting E-Commerce
DAU Count Seems Inflated The growth team reports 5,000 DAU but the product team says they only have 2,000 registered users. The query: SELECT COUNT(customer_id) FROM orders WHERE DATE(created_at) = CURRENT_DATE.
Tables: orders, customers
medium Aggregation Error E-Commerce
Average Order Value is Too Low Analytics shows AOV is $45 but the product team expects ~$85 based on pricing. SELECT AVG(total_amount) FROM orders runs without errors.
Tables: orders, order_items
medium Join Explosion E-Commerce
Revenue Doubled After a JOIN A junior analyst added a JOIN to include product categories. Revenue went from $500K to $1.1M. The query is syntactically correct.
Tables: orders, order_items, products, categories
medium Pipeline Failure Finance
Pipeline Stopped Loading Transactions The fraud dashboard stopped flagging suspicious transactions 3 days ago. The data team says the pipeline is green but analysts see no new data.
Tables: transactions, accounts
medium Timezone Bug E-Commerce
Daily Metrics Shift by Timezone The daily revenue report runs at 9 AM EST. Yesterday's numbers change slightly each morning. The London office sees different totals than NYC. The query groups by DATE(created_at).
Tables: orders
medium Refund Miscalculation E-Commerce
Revenue Doesn't Match After Refunds Finance reports net revenue as $850K. Accounting says it should be $720K after refunds. The JOIN with refunds seems correct syntactically.
Tables: orders, order_items
medium Business Logic Finance
Loan Payments Exceeding Loan Amount The audit team found customers who have paid more than their original loan amount. This shouldn't happen — once a loan is paid off, payments should stop.
Tables: accounts, transactions
medium Referential Integrity Healthcare
Lab Results for Non-Existent Visits The lab information system shows results for visits that don't exist in the main system. This creates compliance issues — results must be linked to valid visits.
Tables: lab_results, visits
medium Status Filter Bug E-Commerce
Cancelled Orders Counted in Revenue Monthly revenue is $50K higher than what actually hit the bank. The finance query: SELECT SUM(total_amount) FROM orders WHERE DATE(created_at) >= '2024-01-01'. It doesn't filter by status.
Tables: orders
medium Incorrect Metric Finance
Customer Churn Rate Seems Too High Dashboard shows 75% customer churn, but the business team says retention is healthy. The churn query counts customers with no transactions in 90 days vs total customers.
Tables: accounts, transactions, customers
medium Duplicate Data Healthcare
Duplicate Billing for Same Visit Patients are complaining about being billed twice for the same visit. The billing department says their system shows one bill per visit, but the database tells a different story.
Tables: billing, visits, patients
medium Filter Bug E-Commerce
Product Category Revenue Mismatch The product dashboard shows Electronics as #1 by revenue, but the sales team says Clothing should be #1. The dashboard query joins products with order_items.
Tables: orders, order_items, products, categories
medium Pipeline Gap Finance
Transaction Sequence Has Gaps The audit team requires sequential transaction IDs with no gaps for compliance. They found gaps in the transaction_id sequence and flagged it as a potential fraud indicator.
Tables: transactions
medium Business Logic Healthcare
Abnormal Lab Results Without Follow-up A clinical audit found patients with critical lab results (e.g., very high blood sugar) who never had a follow-up visit. This is a patient safety issue.
Tables: lab_results, visits, patients
medium Calculation Error E-Commerce
Inventory Count Doesn't Match Sales The warehouse says product X should have 100 units in stock (started with 500, sold 400). But the system shows 250 units. Something is wrong with how sold quantity is calculated.
Tables: products, order_items, orders
medium Duplicate Counting Healthcare
Patients Counted Multiple Times in Census The hospital census report shows 3,000 active patients, but registration says they only have 2,100 unique patients. The census counts from the visits table.
Tables: visits, patients
medium Calculation Error Finance
Running Balance Out of Sync Several customers report that their account balance displayed doesn't match their transaction history. The balance should equal deposits minus withdrawals.
Tables: accounts, transactions
medium Business Logic E-Commerce
Discount Applied Twice Customers are getting double discounts — a 20% coupon discount is applied, and then the order total is also reduced by the same amount. The discount column exists in both orders and order_items.
Tables: orders, order_items
medium Timezone Bug E-Commerce
Weekend Orders Show as Weekday The weekend marketing campaign shows zero weekend orders, but the campaign definitely drove traffic. The analytics query uses EXTRACT(DOW FROM created_at) to identify weekends.
Tables: orders
medium Pipeline Gap Healthcare
Missing Monthly Billing Periods The CFO noticed that some months have zero billing activity in the reports. This is impossible — the hospital operates 24/7. Something is wrong with the data.
Tables: billing, visits
medium Business Logic E-Commerce
Reviews from Non-Customers The product team suspects fake reviews. Some reviews are from users who never purchased the product. The review system should require a purchase.
Tables: orders, order_items, customers
medium Race Condition Finance
Account Balance Goes Negative After Transfer Two simultaneous transfers from the same account succeeded, but the account only had enough balance for one. The resulting balance is negative.
Tables: transactions, accounts
medium Temporal Anomaly E-Commerce
Order Created Before Customer Registration QA found orders with created_at dates earlier than their customer's registration date. How can someone order before they registered?
Tables: orders, customers
medium Data Type Truncation Finance
High-Value Transactions Missing from Report The compliance report for high-value transactions (over $10,000) is missing records. The team knows there were 50 such transactions but the report only shows 35.
Tables: transactions, accounts
medium Business Logic Healthcare
Appointments Double-Booked Doctors are complaining about double-booked appointment slots. The scheduling system should prevent two patients from booking the same doctor at the same time.
Tables: visits, doctors, patients
medium Business Logic E-Commerce
Products Sold Below Cost The finance team discovered that some products are being sold below their cost price, resulting in negative margins. This shouldn't happen unless explicitly approved.
Tables: products, order_items, orders
medium Incorrect Metric Finance
Monthly Active Accounts Miscounted The executive dashboard shows Monthly Active Accounts growing 20% MoM, but new account signups are flat. The MAA query counts accounts with any transaction in the month.
Tables: accounts, transactions
medium Business Logic Healthcare
Prescription Without Diagnosis The pharmacy audit found prescriptions issued without a recorded diagnosis. For compliance, every prescription must be linked to a diagnosis from a visit.
Tables: visits, patients, doctors
medium Calculation Error E-Commerce
Shipping Cost Calculated Wrong Customers are being charged shipping based on the number of order_items rows instead of total weight or order value. An order with 3 items pays 3x shipping.
Tables: orders, order_items
hard Duplicate Data E-Commerce
Revenue is 30% Higher Than Expected The finance team runs a monthly revenue report. This month, the dashboard shows revenue is 30% higher than the sales team's manual tracking. The CFO needs this resolved before the board meeting tomorrow.
Tables: orders, order_items, payments
hard Multi-Source Mismatch E-Commerce
Cross-Dataset Revenue Reconciliation Fails The data warehouse team can't reconcile revenue between the orders table and the payments table. Orders show $1.2M, payments show $1.05M. Both should match.
Tables: orders, order_items, payments
hard Funnel Logic Error E-Commerce
Conversion Funnel Shows Impossible Drop The marketing funnel shows: 10,000 visitors → 8,000 add-to-cart → 9,000 purchases. Purchases can't exceed add-to-carts. The funnel data comes from counting distinct customer_ids at each stage.
Tables: orders, order_items, customers
hard Statistical Error Finance
YoY Growth Calculation is Misleading The executive report shows 200% YoY revenue growth. While growth is real, the actual number seems inflated. The calculation compares this month's revenue to the same month last year.
Tables: transactions, accounts
hard Window Function Bug Healthcare
Readmission Rate Calculation is Wrong Hospital readmission rate shows 45% — far above the 15% national average. CMS penalties are calculated on this. The query counts patients who visit within 30 days of a prior visit.
Tables: visits, patients, doctors
hard Data Type Issue Finance
Revenue Leaking Through Currency Conversion International transactions are losing money in currency conversion. The stored USD amount doesn't match the expected conversion from the original currency. Differences are small per transaction but add up to $50K/month.
Tables: transactions, accounts
hard Cohort Analysis Bug E-Commerce
Cohort Retention Curve is Flat The retention analysis shows 90% retention at month 12 — impossibly high. The cohort query assigns customers to their first order month and checks if they ordered in subsequent months.
Tables: orders, customers
hard Pattern Detection Finance
Fraud Detection Missing Structured Transactions The fraud team suspects structuring — customers splitting large transactions into multiple smaller ones just under $10,000 to avoid reporting. The current alert only checks individual transaction amounts.
Tables: transactions, accounts, customers
hard Survivorship Bias Healthcare
Patient Mortality Rate Seems Wrong The hospital's 30-day surgery mortality rate is calculated as deaths within 30 days divided by total surgeries. It shows 2%, but when compared to national benchmarks it should be 5% for this case mix.
Tables: visits, patients
hard SCD Issue Finance
Slowly Changing Customer Data Breaks Joins The regulatory report shows fewer customers than expected. The report joins transactions with the customers table on customer_id. But some customers changed their details (name, address) and got new customer_ids in the process.
Tables: customers, accounts, transactions
hard Statistical Error E-Commerce
Dashboard Shows Wrong Percentile The performance dashboard shows the 95th percentile response time as 200ms, but users complain about 5-second delays. The monitoring team says the p95 query uses PERCENTILE_CONT.
Tables: orders
hard Double Entry Error Finance
Inter-Account Transfers Creating Ghost Money The total money in the system (sum of all account balances) increased by $100K in a month, but no new deposits came in from outside. Internal transfers should be zero-sum.
Tables: transactions, accounts
hard Cross-Record Analysis Healthcare
Medication Interaction Alerts Missed A patient had an adverse drug reaction because the system didn't flag a dangerous combination. The patient was prescribed Drug A by one doctor and Drug B by another, on different visits.
Tables: visits, patients, doctors
hard Statistical Error E-Commerce
Seasonal Trend Masked by Growth The CEO says 'every month is our best month ever' and questions why marketing spends more during holidays. The monthly revenue chart shows a consistent upward trend with no seasonality.
Tables: orders, order_items
hard Data Migration Bug Finance
Credit Score Band Distribution Shifted After a database migration, the credit score distribution shifted. 80% of customers now show 'Excellent' credit — previously it was 30%. The scores themselves look correct.
Tables: customers, accounts
hard Timestamp Arithmetic Healthcare
Patient Wait Time Calculation Off by Hours The patient experience dashboard shows average wait time is 15 hours. This is impossibly high — real wait times are 30-90 minutes. The query: AVG(visit_start - check_in_time).
Tables: visits, patients
hard Attribution Error E-Commerce
Referral Attribution is Double-Counting The marketing team claims each channel is responsible for $2M in revenue. But total revenue is only $3M. The sum of channel attributions is $8M. Each channel is taking full credit for shared customers.
Tables: orders, customers
hard Calculation Error Finance
Interest Calculation Compounds Wrong Savings account interest payments are consistently lower than what customers expect. The system uses simple interest but the product was sold as compound interest. The difference grows over time.
Tables: accounts, transactions
hard Classification Error Healthcare
Emergency Visits Classified as Routine The ER department's capacity report shows 30% fewer emergency visits than security badge-ins count. The report filters by visit_type = 'emergency' but some ER visits are miscoded.
Tables: visits, patients, doctors
hard Metric Definition Error E-Commerce
Cart Abandonment Rate Seems Too High The cart abandonment rate shows 95% — meaning 95% of carts are abandoned. Industry average is 70%. The product team insists the checkout flow works fine. The metric: 1 - (orders / carts_created).
Tables: orders, customers
hard Survivorship Bias Finance
Loan Default Rate by Vintage is Wrong Recent loan vintages show 0% default rate, making older vintages look much worse. The CEO wants to know why old lending standards produced more defaults.
Tables: accounts, transactions
hard Statistical Error E-Commerce
A/B Test Shows Both Variants Win An A/B test ran for 2 days. Variant A shows higher conversion rate in the first day, Variant B shows higher in the second day. The product team says both variants are better and wants to ship both.
Tables: orders, customers
hard ETL Dedup Failure E-Commerce
Data Lake Has Duplicate Records After Merge After merging data from two source systems, the data lake has 600K orders instead of the expected 400K. Some orders exist in both systems and were loaded twice.
Tables: orders, order_items, customers
hard Overlap Counting Healthcare
ICU Bed Utilization Shows Over 100% The hospital capacity dashboard shows ICU bed utilization at 120%. The hospital has 50 ICU beds but the dashboard says 60 are occupied. This should be physically impossible.
Tables: visits, patients, doctors
hard Metric Paradox E-Commerce
Customer Lifetime Value Drops After Fix After fixing a double-counting bug in revenue, the reported Customer Lifetime Value (CLV) dropped by 40%. The CEO is upset. CLV = total revenue / total customers. But the revenue fix was correct.
Tables: orders, customers, order_items