The assignment: give a sales manager a number they can use
Your fictional manager asks: “How much open pipeline did we have at 6 p.m. UTC on September 15, and what needs attention before quarter-end?” You receive 26 export rows, not 26 independent deals. Some are older snapshots, one is a retried row, and one contains information that did not exist at the reporting cutoff. Your deliverables are a selected-opportunity table, a short reconciliation and a recommendation of no more than 200 words.
Use a spreadsheet, SQL or Python. The core task needs sorting, filtering, a join and arithmetic; it does not require a predictive model. Read rules.json before opening the reference solution. It defines the cutoff, currency, stage probabilities and stale-activity rule. All organizations, records and outcomes are invented for this exercise.
Salesforce describes a pipeline as prospects moving through a sales process, with stages helping identify next actions and delays. Here the goal is narrower: create a trustworthy dated view and identify an actionable exception. A weighted pipeline is not signed revenue, cash receipts or a calibrated forecast.
Data and worked inputs
Sources[1]
One row in your output must mean one opportunity at one cutoff
Start by preserving the raw file. Parse snapshot_at as a timezone-aware timestamp and retain rows at or before the stated cutoff. The September 17 update must not enter a September 15 report, even though it is the latest row in the file. Retrospective analysis that uses future information answers a different question.
Next resolve repeated opportunity-and-timestamp keys. The two O03 rows have identical business fields and different export-row labels, so one is a retry. Remove the retry from the analytical view, not from the original file. A tied timestamp with different amounts or stages would be a conflict. Stop and report that conflict rather than taking the larger amount, the first row or whichever answer looks more plausible.
After resolving ties, select the latest eligible snapshot for each opportunity. Keep O01 and O12: they belong to the same account but are different deals. Deduplicating by account would erase a legitimate opportunity. Only after selecting the dated version should you filter by open status; otherwise an old open row can survive after the latest row has closed.
Reconcile the population before presenting its value
Your selected view has twelve opportunities. Nine are open; one is closed-won and two are closed-lost. Their all-status amount is $301,000, of which $57,000 belongs to the three closed opportunities. The open total is therefore $244,000. The raw export contains one future row and one retry; older snapshots are superseded, not additional sales.
| Checkpoint | Expected result |
|---|---|
| Latest eligible opportunities | 12 |
| Open opportunities | 9 |
| All-status selected amount | $301,000 |
| Open amount | $244,000 |
| Weighted open amount | $126,200 |
| Quarter-end open amount | $100,000 |
| Weighted quarter-end amount | $58,400 |
Join each selected row to stage_rules.csv. Treat a stage absent from that table as an error, not a zero-probability deal. Multiply each open amount by its stipulated stage probability and sum the products. The result is $126,200. Do not average the three stage probabilities and apply that average to total pipeline; deal amounts differ.
For a quarter-end view, retain open opportunities whose expected closing dates fall between the reporting date and September 30, inclusive. That yields $100,000 of nominal pipeline and $58,400 after the same stage weights. Expected closing dates are planning fields, not proof the deals will close in that interval.
Find the deal that could change the conversation
O03 is a $30,000 negotiation-stage opportunity expected to close on September 28. Its last recorded activity is August 20, twenty-six calendar days before the reporting date. The exercise marks activity older than fourteen days as stale. Its weighted contribution is $24,000, about 41.1% of the weighted quarter-end total.
That concentration gives the manager a specific next action: ask the owner to verify the buyer’s next step, expected closing date and stage evidence. Do not remove O03 merely because it is stale. The record supports a review request, not a claim that the deal is lost. Likewise, do not renew the activity date simply to make the report look healthier.
Show a sensitivity beside the baseline. Excluding O03 from the quarter-end planning view reduces nominal pipeline to $70,000 and weighted pipeline to $34,400. Label this a sensitivity, not a corrected forecast. It tells the reader how much one unresolved record matters without pretending you already know the outcome.
A complete example of the manager-facing output
The memo should lead with the answer, name the reporting cutoff and give one decision. Put the detailed record-selection rules in a short methods note rather than opening with a tour of your spreadsheet. A useful reader should understand both the scale and the uncertainty in one pass.
Original fictional manager memo
Build your answer before running the reference analysis
Submit a table containing opportunity ID, selected timestamp, amount, stage, stage weight, weighted amount, closing date and stale flag. Add an exclusion log for the future update, retry and superseded versions. The log should make it possible to recover the raw rows you excluded.
Then change one business field on the duplicate O03 row while keeping its key unchanged. Your method should reject the contradictory tie. Shuffle the raw rows and rerun; results should not depend on export order. Move the reporting cutoff earlier than the September 15 snapshots and verify that older records become eligible without borrowing later facts.
The download includes a runnable reference and a question-only submission file. Comparing your results with the reference is useful after you can explain the method. A completed script is not a substitute for understanding why status filtering belongs after version selection. The supplied implementation is a teaching reference for this data contract, not a production CRM connector.
Use the optional SQL track to demonstrate the query itself
The optional SQL version makes the order of operations visible. Its eligible view applies the reporting cutoff; distinct_snapshot resolves exact retries; latest_opportunity ranks versions within each opportunity; only then does open_opportunity apply the status rule. Inspect each view. Filtering open status before selecting the latest version would retain a superseded deal state.
The local runner imports the CSVs into an in-memory SQLite database and normalizes timezone-aware timestamps. Run python run_sql.py for the reference, or copy sql-starter.sql and run python run_sql.py your-analysis.sql after writing your own exceptions and report views. Python 3.10+ and SQLite 3.25+ are required for this route. No hosted database or account is needed.
Test a contradictory O03 tie, an absent stage rule and a shuffled export. A row_number function alone does not decide which tied business record is true. The query must reject that conflict before its final report is accepted. Explain why account_id is useful context but not the partition key for this opportunity-level analysis.
Data and worked inputs
Sources[2]
Turn the analysis into credible application evidence
A concise project description is: “Analyzed a synthetic CRM export, reconstructed a dated opportunity view and isolated a stale deal representing 41% of weighted quarter-end pipeline.” Use that only after doing the work. It describes the analysis rather than claiming you recovered revenue or improved a real forecast.
For a sales-operations interview, explain how you chose the row key and reporting date. For a business-analytics interview, explain the join, validation and sensitivity. A natural follow-up is why two deals at the same account survived while the duplicated O03 row did not. Another is whether you can estimate conversion accuracy from this file. You cannot: it lacks the later outcomes required to assess those stage weights.
Make the project your own by adding a different reporting cutoff, one documented exception or a comparison of two reproducible methods. Preserve the original case inputs and distinguish your extension. The role guide below explains where this work fits; this page is the deeper build-and-defend exercise.