Skip to content

README.md

Latest commit

 

History

History
90 lines (68 loc) · 3.31 KB

README.md

File metadata and controls

90 lines (68 loc) · 3.31 KB
Raw

Analytics Engineer Case Study

Your Mission

As an Analytics Engineer, you'll build the data infrastructure that powers product decisions, customer insights, and business intelligence across Riot. It's representative of the kind of project you could pick up on the team.

The Exercise: Revenue (ARR) Modeling from Stripe

Scenario

Finance needs to track how revenue evolves over time, customer by customer. Today they pull numbers from Stripe by hand and can't answer basic questions: what's our ARR today, how it has moved over time, which customers grew and which shrank.

You've just extracted three files from our Stripe account. Build clean, reusable data models that turn this raw billing data into a reliable view of ARR and its evolution.

The Data

  1. stripe_customers.csv
  2. stripe_subscriptions.csv
  3. stripe_invoices.csv

Data Dictionary:

stripe_customers.csv

  • customer_id: Stripe customer id.
  • metadata_workspace_id: Links to our product database (each Stripe customer = one RIOT Workspace).
  • metadata_organization_id: If present, this Workspace belongs to an Organization (our enterprise feature: one parent entity managing multiple Workspaces). Multiple customers can share the same organization_id.
  • created_at, country, email, name.

stripe_subscriptions.csv

  • subscription_id, customer_id.
  • product_id, product_name: the Stripe product the customer subscribes to.
  • status: active, canceled, past_due, trialing.
  • mrr: current Monthly Recurring Revenue in USD.
  • billing_interval: month or year.
  • current_period_start, current_period_end: the subscription's current billing period.
  • created_at, canceled_at.
  • metadata_modules: comma-separated list of the RIOT modules provisioned on this subscription (modules: Awareness, Simulation, Slash, Sonar, Studio, Smishing, Vishing).

stripe_invoices.csv

  • invoice_line_id, invoice_id: an invoice can have several lines.
  • customer_id, subscription_id.
  • period_start, period_end: the service period this line covers.
  • amount: line amount in USD (currency).
  • item_type: recurring, proration, or one_off.
  • created_at.

Your Task

Part 1: Build the data model

Build a dbt project that models ARR from these sources: your staging and mart models, plus the data-quality tests you'd ship with them. You don't need to actually run it, but structure it as a real dbt project (models, schema.yml, tests) we could dbt build. Tell us the grain of each model and how someone would use these tables to answer the questions below over time.

Part 2: Write SQL queries

Write SQL to answer these questions, against your models or the raw CSVs directly:

Query A: Current ARR

As of the most recent month in the data, what is the company's total ARR,
and what is each customer's ARR?

Query B: ARR over time

Show the company's total ARR at the end of each month over the available history.

Query C: ARR movement

Choose a start month and an end month (state which). For each customer whose ARR changed
between them, return one row showing the customer, the change in their ARR, and the type
of movement: new, expansion, contraction, churn, or reactivation.

Document any assumptions you make. We're as interested in how you reason about ambiguity as in the final numbers.