Most recent opportunity per account

Your forecast is off and the CRO wants to know why

A Salesforce-shaped RevOps schema with planted history-table realism. Opportunities move forward and sometimes backward through stages, deals get reassigned mid-cycle, Closed-Won deals occasionally reopen and inflate the active pipeline, regions and reps have uneven quota attainment, and the OpportunityHistory SCD2 captures every stage / amount / owner change with timestamps. The exact data shape RevOps analysts see on day one at any SaaS company on Sales Cloud.

The SQL patterns RevOps, Sales Ops, and Revenue Analytics teams actually write — OpportunityHistory SCD2 point-in-time snapshots, pipeline stage regressions, mid-cycle owner reassignments, Closed-Won reopens that inflate pipeline, top-N-per-region rep rankings, sales cycle velocity, ARR waterfall bridges, and forecast-accuracy backtests — against a Salesforce-shaped dataset with realistic dirty history. Written for career changers with any bachelor’s degree — SaaS B2B analytics roles (Sales Ops Analyst, RevOps Analyst, Revenue Analyst) pay $75–110K entry-level and hire on portfolio work like this.

Account, Contact, Lead, Opportunity, OpportunityHistory, User. The exact table shape you’ll see on day one of any Sales Cloud shop, with the field names you’ll recognize from real Salesforce exports.

Salesforce-Shaped Schema

Every stage change, amount change, owner change logged as a row. Point-in-time reconstruction (“what did the forecast look like on March 15?”), stage regression detection, and the Closed-Won reopen anti-pattern that invalidates 80% of naive pipeline reports.

OpportunityHistory SCD2

Stage-duration windows, rep win-rate by region, top-N per group, quarter-over-quarter bookings waterfalls, forecast accuracy as a backtest. The full RevOps analyst toolkit.

Sales Cycle + Velocity SQL

Every mission maps to a published RevOps / Sales Ops analyst prompt (Salesforce, Gong, Outreach, ZoomInfo, HubSpot, Databricks, Zscaler). Pair with a Salesforce Admin cert (~$200, 1 month) and you have a hiring edge.

Interview + Job-Ready

Your forecast is off by $1.2M. Closed-Won reopens again.

Meet the opportunities table

What stages exist in the pipeline?

Open opportunities by owner

Drop the Closed-Lost deals

Deals over $50K by account

Pipeline value by stage

Deals by close quarter

Win rate by sales rep

Average deal size by region

Leads with no account match

Deals that reopened after Closed-Won

Stage regressions — deals that moved backward

Mid-cycle owner reassignments

Top 3 reps per region by closed-won revenue

Pipeline velocity — avg days per stage

Point-in-time forecast snapshot (SCD2)

Closed-Won cohort retention by month

Quarterly bookings waterfall — new vs expand

Amount discount — committed vs closed

Pipeline hygiene — issue count per opportunity

ARR bridge — starting + new + expand per quarter

Sales cycle segmentation by deal-size bucket

Rep performance dashboard

Board-ready sales operations summary

Discount slippage by close-quarter

Stage conversion funnel with drop-off

Regional pipeline scorecard

Account expansion analysis — multi-opp customers

RevOps board capstone — quarter close panel

user_id

full_name

role_title

region

hire_date

status

revops_users

account_id

company_name

industry

employee_band

country

created_date

revops_accounts

contact_id

email

title

revops_contacts

opportunity_id

owner_user_id

name

stage

amount

close_date

is_closed

is_won

revops_opportunities

history_id

changed_field

old_value

new_value

changed_by_user_id

changed_at

revops_opportunity_history

lead_id

source

revops_leads

Most recent opportunity per account

The Brief

You'll practice