SQL Developer
Active
Intermediate ~4h

Revenue Discrepancy Investigation

Reconcile a $400K variance before the 2 PM exec review.

Progress40% • due Today, 02:00 PM

The situation

Finance is reporting $3.5M in revenue for last week, but the Analytics dashboard shows $3.9M. Leadership needs a clear, defensible explanation in five hours.

Context

  • Finance pulls weekly revenue from the billing system at close of business Friday.
  • Analytics rebuilds revenue from raw events nightly and includes pending refunds.
  • A refund batch for 10 May was imported late by Data Engineering.

Your objectives

  • Identify the source of the $400K variance.
  • Quantify the refund timing impact vs. genuine data issues.
  • Recommend a single source of truth for weekly revenue.

Phases

  1. Frame the problem

    Confirm definitions of revenue across teams.

  2. Pull comparable datasets

    Run Finance and Analytics queries for the same window.

  3. Isolate the variance

    Now

    Break down the gap by product line and refund cohort.

  4. Draft recommendation

    Propose a reconciliation process and owner.

  5. Defend at exec review

    Present findings to CFO and Finance Lead.

Tasks

  • Compare Finance vs Analytics revenue by day
    10:00 AM
  • Quantify refund timing impact
    11:30 AM
  • Validate product-line breakdown with Kavya
    12:30 PM
  • Draft one-page summary for CFO
    01:30 PM
  • Send pre-read to Rohit
    01:45 PM

Inbox for this scenario

Open inbox
MC

Meera Chandra · CFO

09:15 AM

Revenue numbers don't match

Viplav, Finance is showing $3.5M for last week but Analytics has $3.9M. I need an explanation before 2 PM.

Urgent
RF

Rohit Fernandes · Finance Lead

09:20 AM

Need update before 1 PM

We have the leadership review at 2 PM. A short pre-read would help me brief Meera.

High
KD

Kavya Desai · Data Engineering

09:25 AM

Refund data import completed

The refund batch for 10 May has been imported. It was delayed by ~36 hours.

FYI

Success criteria

  • Variance is reconciled to within $5K.
  • Root cause is documented with supporting SQL.
  • Recommendation is accepted by Finance and Analytics leads.

Stakeholders

  • MC

    Meera Chandra

    CFO

    tense
  • RF

    Rohit Fernandes

    Finance Lead

    neutral
  • KD

    Kavya Desai

    Data Engineering

    supportive
  • AS

    Aarav Shah

    Analytics Lead

    neutral

Deliverables

  • Reconciliation SQL notebook

    in review

    Queries + outputs proving the variance source.

  • One-page exec summary

    draft

    Problem, finding, recommendation, next steps.

  • Process recommendation memo

    pending

    Proposal for a single source of truth.

Competencies assessed

  • Query Design82 / 100
  • Root Cause Analysis74 / 100
  • Stakeholder CommunicationWeight 25%
  • Recommendation QualityWeight 20%

Tools

SQL WorkbenchData ExplorerSchema Browser