Lyft Business Intelligence Analyst (Staff Level) - Comprehensive Interview Preparation Guide

Approach
I would define sessions per canonical user ID, not per device, because the requirement says the same user can act from multiple devices. First dedupe duplicate events, then sort by event timestamp, and start a new session when the gap from the previous event is more than 30 minutes.

WITH deduped AS (
  SELECT *
  FROM (
    SELECT
      e.*,
      ROW_NUMBER() OVER (
        PARTITION BY event_id
        ORDER BY ingest_ts DESC
      ) AS rn
    FROM clickstream e
  ) x
  WHERE rn = 1
), ordered AS (
  SELECT
    user_id,
    event_ts,
    CASE
      WHEN LAG(event_ts) OVER (
        PARTITION BY user_id
        ORDER BY event_ts
      ) IS NULL
      THEN 1
      WHEN event_ts - LAG(event_ts) OVER (
        PARTITION BY user_id
        ORDER BY event_ts
      ) > INTERVAL '30 minutes'
      THEN 1
      ELSE 0
    END AS new_session
  FROM deduped
)
SELECT
  user_id,
  event_ts,
  SUM(new_session) OVER (
    PARTITION BY user_id
    ORDER BY event_ts
  ) AS session_id
FROM ordered;

Session metrics
Group by user_id and session_id to get event count, start time, end time, and duration.

Guardrails
Out-of-order events are handled by sorting on event_ts. Duplicates are removed first so they do not create fake activity or split a session.

Direct answer

Run it as four quarterly themes that build from a shared foundation to embedded ownership, governed by a small cross-functional steering group with a lightweight review cadence, measured with both leading and lagging KPIs, funded across a few clear categories rather than one guessed total, and rolled out through a pilot before going company-wide.

Structured elaboration

Quarterly curriculum themes, sequenced so cross-functional work only starts once there's a shared vocabulary:

• Q1, shared data fundamentals: common vocabulary and tooling across analytics, engineering, and product, data modeling basics, how a metric gets defined and where it lives, a SQL literacy baseline for product and engineering, a BI-tool literacy baseline for everyone else.
• Q2, cross-functional data literacy: how each function actually uses BI output, product learns to read funnel and experiment dashboards correctly, engineering learns what data-quality analytics actually needs, analytics learns enough about the underlying systems to debug data issues instead of only reporting them.
• Q3, applied collaboration: joint projects pairing an analyst, an engineer, and a product manager on one real initiative each, for example instrumenting a new feature correctly from day one, which is where most real skill transfer happens.
• Q4, ownership and scale: teams take on defining and owning their own metrics and dashboards with analytics as a reviewer rather than a builder, shifting the program from centrally-run to embedded practice.

Governance model: a small steering group, one lead each from analytics, engineering, and product, plus the program sponsor, meeting monthly to unblock and quarterly to formally review, under a lightweight charter defining what's mandatory versus optional so the program doesn't become a bureaucratic side-project.

KPIs for success, split leading (early signals that predict success) versus lagging (the outcome itself, seen later): leading KPIs are course or module completion rate, cross-functional project pairings actually completed, and skill self-assessment score change before and after each quarter; lagging KPIs are a falling rate of data-quality tickets or report-defect escalations, a shrinking average time from a product question to a trustworthy answer, and growth in the number of dashboards owned directly by non-analytics teams without analytics as a bottleneck.

Budget categories, as a planning framework rather than a claimed real figure: training and course licenses; a small conference or certification allowance per participant; internal mentor time, budgeted as a real percentage of mentors' capacity rather than assumed free; tooling and sandbox environment costs for hands-on practice without touching production; and a contingency line for the inevitable unanticipated cost.

Mentoring structure: cross-functional pairs, one analytics person paired with one engineer or product manager for that quarter's applied project, plus a lighter rotating office-hours model for quick questions so it doesn't fall on the same one or two people every time.

Rollout approach: pilot with one product area for Q1-Q2 before a company-wide Q3-Q4 rollout, so the curriculum gets corrected against real feedback before scaling, and the pilot's visible wins become the internal case for broader buy-in.

Worked example

Applying the framework to a forty-person analytics, engineering, and product org, piloting with the product growth team, roughly ten people, in Q1-Q2. Q1: shared-fundamentals workshops delivered to the pilot team. Q2: cross-functional literacy sessions plus a first joint project, instrumenting a new onboarding flow correctly, pairing the growth product manager, an analyst, and an engineer. By the end of Q2, that team's dashboard-defect rate, self-reported "this number looks wrong" escalations, is the concrete before-and-after case brought to the steering group to justify the Q3 company-wide rollout.

Trade-offs and pitfalls

Making participation mandatory for everyone increases reach but dilutes engagement and often becomes a checkbox exercise; volunteer-only increases genuine engagement but under-reaches the people who need it most and risks the program becoming an analytics-only fan club. Central analytics-led delivery is faster to stand up but doesn't scale and keeps analytics as a bottleneck, which undercuts the Q4 goal of embedded ownership. Heavy KPI measurement, surveys and skill checks, adds real overhead that competes with the learning time it's meant to protect, so measure enough to prove value, not everything that's measurable.

Requirements & constraints:

• 10,000 users, 50TB analytics-ready data, sub-minute dashboard response SLA for common queries, concurrent user spikes, multi-tool support (Tableau, Power BI, Looker), strict governance and single source of truth (SSOT).

High-level architecture:

• Ingest → Raw Storage (data lake) → Central Data Warehouse (columnar MPP) → ELT pipelines + Semantic Layer → BI Tools → Cache / Query Acceleration → Auth & Monitoring

Core components:

1. Central Data Warehouse: cloud MPP (Snowflake / BigQuery / Azure Synapse). Stores modeled, partitioned, and access-controlled fact & dimensional tables. Use separate schemas for raw, curated, and marts.
2. ELT pipelines: orchestration with dbt + Airflow (or native cloud orchestrator). Transformations implemented as version-controlled dbt models; tests enforce data quality.
3. Semantic layer: two-tier approach:
   • Canonical metric layer in the warehouse implemented as materialized views / dbt exposures (the true metric calculations).
   • Tool-aware semantic adapters: LookML referenced to warehouse views; Power BI shared datasets or Azure Analysis Services / Fabric semantic model; Tableau published data sources pointing to the same warehouse views.
4. Metric registry & catalog: centralized metric registry (e.g., open-source or commercial like Metrics Layer / Transform) that stores metric definitions, lineage, owners, tests, and versioning. Expose via API and sync to LookML, PBI datasets, and Tableau prep.
5. Authentication & SSO: enterprise SSO via SAML/OIDC + SCIM for user provisioning (Okta/Azure AD). Row-level security enforced at warehouse views with role-mapped policies; BI tools inherit or enforce additional RLS where necessary.
6. Caching & acceleration: use warehouse result caching + a query acceleration layer (Cube/Apache Druid/Materialize) for high-concurrency dashboards. Also leverage BI tool extracts where low-latency offline needed with scheduled refreshes.
7. Monitoring & observability: data pipeline monitoring (dbt Cloud/Airflow alerts), warehouse cost & query monitoring, BI usage analytics (who uses what), SLA alerts, and data quality dashboards. Implement lineage tracing (OpenLineage) to attribute issues to source.
8. Governance & SSOT practices:
   • Implement canonical metric definitions in dbt as single source; enforce via CI (tests) and PR reviews.
   • Metric registry is authoritative: BI semantic models must reference registry APIs or warehouse views—no ad-hoc metric SQL in dashboards.
   • CI/CD for semantic models: changes to metric definitions require tests, sign-off from metric owner, and automated propagation to LookML / PBI / Tableau connectors.
   • Access control: least privilege, audited queries, and immutable snapshots for regulatory needs.

Data flow summary:
Raw data -> staged in data lake -> loaded to warehouse (ELT) -> dbt transforms produce canonical metric views (materialized where needed) -> metric registry syncs definitions -> BI semantic adapters (LookML, PBI dataset, Tableau published data sources) reference those views -> optional acceleration layer for high-concurrency -> dashboards.

Trade-offs:

• Single canonical layer in warehouse favors consistency and auditability but increases warehouse compute costs; mitigate with selective materialization and caching.
• Tool-specific semantic layers required for best UX; enforce linkage to the canonical views via automated sync to avoid divergence.

Operational notes:

• Start with pilot: implement core metrics, connect one team using all three tools to validate registry-sync workflows.
• Enforce training, documentation, and periodic audits to maintain SSOT.

Direct answer

Don't declare the old number wrong and switch overnight. Explain the change in terms partners can verify for themselves, run both definitions side by side for a defined period so people can reconcile the gap at their own pace, and give a concrete accounting of why the numbers differ before asking anyone to adopt the new one as their working reality.

Structured elaboration

1. Find out what's actually anchored to the old number. It's rarely the number itself that people resist, it's the targets, dashboards, or comp plans built on top of it. Identify those dependencies before you talk about the redefinition in the abstract.
2. Show a concrete case where the old definition misled someone. An abstract "this is more accurate" argument doesn't land. A specific example where the old calculation gave a wrong or misleading answer does.
3. Run dual reporting, don't hard-cutover. Publish both the old and new metric side by side for a fixed window so partners can watch the two track each other (or diverge) and build intuition for the new number before they have to rely on it alone.
4. Break the gap into named components. Instead of "the number moved," account for the difference: how much of the change comes from the new inclusion/exclusion criteria, how much from a data-quality fix, how much from a genuine behavior shift. A gap people can decompose feels explainable; an unexplained gap feels arbitrary.
5. Set an explicit cutover date and update every downstream artifact by name, dashboards, target-setting docs, comp formulas, rather than assuming people will notice and adjust on their own.
6. Keep the old metric available, read-only, for a grace period after cutover instead of deleting it immediately, so people can still check their own prior conclusions against it while they adjust.

Worked example

Suppose "active users" currently counts anyone who logs in during the month. The new definition additionally requires at least one core in-product action during that session, because the team found that a meaningful share of logins were automated health-checks or bounced sessions that didn't reflect real engagement. If the old metric counted 10,000 monthly logins, and historically about 30% of logins involve no core action (a figure pulled from existing session logs, not asserted), the new definition would show roughly 10,000 x (1 - 0.30) = 7,000 active users, a drop of 3,000 driven entirely by the new inclusion criterion, not by an actual usage decline. Dual reporting both numbers for a month, with that 3,000-user gap explicitly labeled "removed for lacking a core action, not a real drop," lets a marketing partner whose Q3 target was set against the old 10,000-count number understand exactly why their dashboard changed before they have to defend it to their own leadership.

Trade-offs & pitfalls

• Pitfall: cutting over immediately without a dual-reporting window. It looks like the number was changed to hit or dodge a target, even when it wasn't.
• Pitfall: mandating adoption from authority ("this is the new source of truth, use it") without walking anyone through the why. Technically correct, but it burns trust and invites people to quietly keep using their own old tracking.
• Pitfall: deleting the old metric immediately, which strands anyone mid-adjustment and turns a change-management problem into an access problem.
• Senior differentiator: treating a metric redefinition as a change-management effort you own end to end (explanation, parallel run, decomposition, migration of dependents), not just a technical correction you announce and move on from.

Direct answer

Do not rebuild the existing department's star schema from scratch: keep it running, and introduce a compatibility view layer between its existing dimensions and a newly-built conformed version, so its current reports keep working unmodified while every new mart is built against the conformed dimensions from day one. Sequence the three new departments' marts in whichever order matches the bus matrix's widest-touching dimensions first, exactly as you would for a greenfield build, since from their point of view they are joining an enterprise warehouse that already has its shared dimensions defined.

Structured elaboration

What to do with the existing star schema. The existing department's fact table and dimensions almost certainly still work correctly for that department's own reports; the problem is only that its dimensions were never designed to be reused by anyone else. Do not touch the fact table. Build new, properly conformed versions of whichever dimensions the other departments will also need (typically customer and date), migrate the existing department's fact table to reference the new conformed dimension's keys, and leave everything else about that department's schema alone.

Introducing conformed dimensions without breaking existing reports. The mechanism that makes this safe is a compatibility view: create a view with the OLD dimension's name and column names, backed by the NEW conformed dimension underneath, so any report or dashboard query that has not been touched keeps running against the old names while the underlying table is now the shared one. Reports get migrated to query the conformed dimension directly (picking up any new attributes it offers) on the team's own schedule, not as a synchronized cutover, and the compatibility view is retired only once nothing depends on it anymore.

Sequencing the migration. For the three new departments, treat this the same way you would size a bus matrix for a greenfield enterprise build (see the sales-orders-first reasoning used when a company plans from scratch): whichever new department's mart shares the most dimensions with the others should be built first against the now-conformed dimensions, so its build validates that the conformed dimensions actually generalize before a second and third department also depend on them.

Worked example

A minimal, runnable illustration of the compatibility-view mechanism:

CREATE TABLE dim_customer_conformed (
    customer_sk   BIGINT PRIMARY KEY,
    customer_id   VARCHAR,
    customer_name VARCHAR,
    region_code   VARCHAR      -- new attribute the conformed dimension adds
);
INSERT INTO dim_customer_conformed VALUES
  (1, 'CUST-100', 'Ada Lovelace', 'EMEA'),
  (2, 'CUST-200', 'Grace Hopper', 'AMER');

-- The original department's dimension name and column names, preserved as a view
-- backed by the new conformed table underneath:
CREATE VIEW dim_customer_v1 AS
SELECT customer_sk, customer_id AS cust_id, customer_name AS cust_nm
FROM dim_customer_conformed;

-- The department's existing report, unmodified, still returns correct results:
SELECT cust_id, cust_nm FROM dim_customer_v1 ORDER BY cust_id;

Executed, the unmodified report query returns [('CUST-100', 'Ada Lovelace'), ('CUST-200', 'Grace Hopper')], exactly what it returned before the migration, while a new report written directly against dim_customer_conformed can immediately use the new region_code attribute the old schema never had.

Trade-offs and pitfalls

The most damaging mistake is treating this as a single cutover weekend: forcing every existing report to move to the new conformed dimension at once maximizes the chance something breaks in production with no fallback. The compatibility view exists precisely so migration can happen gradually, dashboard by dashboard, with the old and new dimensions correct and consistent with each other for as long as both are in use. The second common mistake is skipping the conformance work for a "small" attribute mismatch (say, the old dimension calls it cust_nm and everyone assumes it obviously maps to customer_name), which is exactly the kind of undocumented assumption that produces the four-different-customer-dimensions problem a badly-managed enterprise rollout tends to create.

Direct answer

SELECT c.category_name, SUM(s.quantity * s.unit_price) AS revenue
FROM sales_fact s
JOIN product_dim p ON s.product_key = p.product_key
JOIN category_dim c ON p.category_key = c.category_key
WHERE s.date_key BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY c.category_name
ORDER BY revenue DESC
LIMIT 10;

Snowflaking category_dim out from product_dim adds one extra join to this query (fact to product to category, instead of fact to product with category as a plain column); for this specific query, the extra join is cheap on a modern columnar engine, but it's one more join the optimizer and every future query author has to account for.

Structured elaboration

• Query logic: join the fact table to product_dim to reach category_key, then to category_dim to get the human-readable category_name, filter to the target quarter, aggregate revenue by category, and take the top 10.
• Effect of snowflaking: had category_name been denormalized directly onto product_dim (star schema), this query would need only one join (fact to product) instead of two (fact to product to category). The snowflaked version isn't wrong, just an extra join hop for every query that needs the category name, which adds up in query complexity and, on some engines, execution cost as the fact table and query volume grow.
• Whether to denormalize for reporting: for a category attribute that's genuinely simple, low-cardinality, and queried constantly (as this top-10-by-category query suggests), denormalizing category_name directly onto product_dim (turning this into a plain star schema) is usually the better default for a reporting-focused workload, trading a small amount of storage redundancy for simpler, faster queries and easier business intelligence (BI)-tool ergonomics.

Worked example

For a sample of sales_fact with three categories: Electronics ($45,000), Home Goods ($30,000), Apparel ($12,000) over the quarter, this query returns those three rows (fewer than 10 if the business genuinely only sells in three categories, LIMIT 10 simply returning all available rows), ordered by revenue descending. If category_dim were instead flattened onto product_dim, the same result would come from a single-join query: SELECT category_name, SUM(quantity*unit_price) FROM sales_fact s JOIN product_dim p ON s.product_key=p.product_key GROUP BY category_name.

Trade-offs and pitfalls

Snowflaking is the right call specifically when the category hierarchy is deep, frequently updated in a way that benefits from single-source-of-truth normalization, or shared identically across many unrelated dimensions; for a straightforward, rarely-changing product-to-category mapping used mainly for reporting, star-schema denormalization usually wins on query simplicity without meaningfully increasing storage or maintenance cost, given how well columnar warehouses compress repeated category-name values.

Direct answer

Product and feature profitability means revenue minus the costs that product causes, with shared platform costs allocated by a stated rule. Build it in three layers: (1) trace revenue and direct costs to each product from billing and tagged infrastructure cost; (2) allocate shared platform cost using usage telemetry as the driver, one driver per cost pool (a bucket of shared cost, such as the API gateway bill); (3) present a rule-free contribution margin first (revenue minus the costs directly traceable to the product, before any shared cost), then the after-allocation margin as a range across plausible allocation rules, rounded, with a note on how complete the cost tagging is. The ranking of products can change with the rule, so the honest executive message is the margin range and whether the ranking is stable, not a single precise figure.

Data sources and what each provides

Terms: a direct cost is traceable to one product; a shared platform cost (authentication, the API gateway that routes every request, observability meaning logging and monitoring, and on-call) supports several products; showback means reporting each team's cost without charging it, which is how I would start.

Allocation rules: one driver per pool

Worked line for one pool (illustrative): a $100k API gateway cost split by request count. If Analytics made 15% of the requests it carries 0.15 x $100k = $15k of that pool.

Document each rule: the driver, the data source, the period, who approved it. Freeze the rules for at least a quarter, so a change in margin reflects the business and not a new rule.

Worked example: how much the rule matters

Three products with revenue and direct cost in $ thousands a month (illustrative), and a $400k shared platform cost. The usage share column is each product's measured share of platform compute: for example, if the platform's compute metering shows 150, 550 and 300 compute-hours for the three products in the month, the shares are 15%, 55% and 30%.

# Monthly figures, $ thousands (illustrative). Direct cost = cost-tagged infrastructure + metered third-party usage.
products = {
    #            revenue, direct cost, share of shared-platform compute (telemetry)
    "Analytics": dict(rev=900, direct=270, usage=0.15),
    "Messaging": dict(rev=600, direct=210, usage=0.55),
    "Reporting": dict(rev=300, direct=60,  usage=0.30),
}
SHARED = 400   # shared platform cost: auth, gateway, observability, on-call, $k per month
total_rev = sum(p["rev"] for p in products.values())
rules = {
    "revenue share": lambda p: p["rev"]/total_rev,
    "usage share":   lambda p: p["usage"],
    "equal split":   lambda p: 1/len(products),
}
print(f"shared cost ${SHARED}k, total revenue ${total_rev}k")
for rule, share in rules.items():
    out = []
    for name, p in products.items():
        alloc = SHARED * share(p)
        margin = (p["rev"] - p["direct"] - alloc) / p["rev"]
        out.append((margin, name, alloc))
    out.sort(reverse=True)
    print(f"\n{rule}:")
    for margin, name, alloc in out:
        print(f"  {name:<10} allocated ${alloc:6.1f}k  margin after allocation {margin:6.1%}")
    print("  ranking:", " > ".join(n for _, n, _ in out))
print("\ncontribution margin before any shared allocation (rule-free):")
for name, p in products.items():
    print(f"  {name:<10} {(p['rev']-p['direct'])/p['rev']:.1%}")

Output:

shared cost $400k, total revenue $1800k

revenue share:
  Reporting  allocated $  66.7k  margin after allocation  57.8%
  Analytics  allocated $ 200.0k  margin after allocation  47.8%
  Messaging  allocated $ 133.3k  margin after allocation  42.8%
  ranking: Reporting > Analytics > Messaging

usage share:
  Analytics  allocated $  60.0k  margin after allocation  63.3%
  Reporting  allocated $ 120.0k  margin after allocation  40.0%
  Messaging  allocated $ 220.0k  margin after allocation  28.3%
  ranking: Analytics > Reporting > Messaging

equal split:
  Analytics  allocated $ 133.3k  margin after allocation  55.2%
  Messaging  allocated $ 133.3k  margin after allocation  42.8%
  Reporting  allocated $ 133.3k  margin after allocation  35.6%
  ranking: Analytics > Messaging > Reporting

contribution margin before any shared allocation (rule-free):
  Analytics  70.0%
  Messaging  65.0%
  Reporting  80.0%

The ranking is unstable: Reporting is first under one rule, second under another and last under the third, and Messaging ranges from 28.3% to 42.8%. That is the sensitivity result I would put in front of executives.

Presenting without false precision

1. Lead with contribution margin before shared cost, which has no allocation rule in it, then show margin after allocation.
2. Show the range across rules rather than one number, and round to the nearest 5 points. Here Messaging would be reported as "about 30 to 45%".
3. Say which conclusions survive every rule (Messaging is not the best product under any of them) and which do not (the order of Analytics and Reporting).
4. State data completeness: what share of cloud cost carries a product tag, what share of revenue was split by assumption (bundles), which products have no metering.
5. Feature-level margin follows the same method but with a much weaker revenue link, since a bundled price is not earned by one feature. Report feature-level cost, and usage-weighted margin only as a labelled estimate. For a bundle, a $1,000 monthly plan whose parts list at $600 and $400 is split 60/40 by list price, or by each part's measured usage.
   • Worked example (illustrative, $ thousands a month): Messaging earns 600 and has 210 of direct cost from vendor invoices, which the metering splits into Chat 180 and Email 30. Splitting the 600 by usage (70% Chat, 30% Email) gives Chat 420 of revenue and a contribution margin of (420 - 180) / 420 = 57%, and Email 180 and (180 - 30) / 180 = 83%. Splitting the 600 by list price 50/50 instead gives Chat (300 - 180) / 300 = 40% and Email (300 - 30) / 300 = 90%. The revenue rule alone moves Chat by 17 points, so feature margins are shown as ranges.

Pitfalls

• Allocating shared cost by revenue punishes successful products and can hide a costly one.
• Treating untagged cost as zero; report it separately.
• Changing a driver mid-year and reporting the resulting margin shift as performance.
• Using the allocation to decide pricing or sunsetting without testing the decision against the rule-free contribution margin.

Step 1 — clarify scope quickly

• Ask the PM: which customer segment, product area, timeframe, KPI definition (CSAT, NPS, churn, support CSAT?), and whether there are any recent changes (pricing, releases, outages, campaigns).

Step 2 — validate data sources first

• Primary: CSAT/NPS survey table, support ticket system (Zendesk), product telemetry (events), CRM (segments), billing/ churn data.
• Check survey sample size, response rate, survey timestamps, mapping to users/products; confirm ETL recency and schema.

Step 3 — run initial diagnostics (dashboards / quick queries)

• Trend: CSAT/NPS over time with response count and confidence intervals.
• Segment breakdown: by product, plan, geography, channel, device, and customer tenure.
• Support signals: volume, backlog, first-response and resolution time, ticket CSAT, escalations.
• Product signals: error rates, feature usage drop, release/deploy timeline correlated with CSAT dip.
• Financial signals: churn rate, downgrades, refunds correlated to low CSAT cohorts.

Step 4 — prioritize hypotheses

• Data quality / sampling bias (high priority)
• Recent release/regression causing product issues
• Support degradation (SLAs, staffing)
• Price/plan changes or unexpected billing errors
• Changes in customer mix (onboarding of lower-fit customers)
  Prioritize by impact (how many customers affected) and ease of verifying.

Step 5 — deeper analysis & tests

• Cohort analysis around suspected release date; join error logs with user surveys.
• A/B segment comparisons; survival analysis for churn.
• Drill into qualitative feedback (survey free-text) using keyword counts.

Step 6 — communicate interim findings

• Send a concise summary email within 24 hours: what you checked, top candidate causes, immediate data limitations, recommended next steps and quick wins.
• Share a live dashboard with filters and an executive one-pager highlighting main metric, magnitude of decline, top 3 hypotheses with evidence level, and proposed experiments/patches.
• Schedule a short sync to align on remediation, assign owners, and set follow-up timing.

Outcome/governance: instrument missing metrics (if any), set alerting, and run post-mortem after fixes to measure recovery.

Approach: use a window function to assign row_number() partitioned by (user_id, transaction_id) ordered by updated_at DESC (keep newest). Then delete rows with rn > 1 in one statement using a CTE.

-- Delete duplicates keeping the latest row per (user_id, transaction_id)
WITH ranked AS (
  SELECT
    id,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, transaction_id
      ORDER BY updated_at DESC, id DESC
    ) AS rn
  FROM transactions
)
DELETE FROM transactions
USING ranked
WHERE transactions.id = ranked.id
  AND ranked.rn > 1;

Alternative (create new deduplicated table):

CREATE TABLE transactions_deduped AS
SELECT id, user_id, transaction_id, amount, updated_at
FROM (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, transaction_id
      ORDER BY updated_at DESC, id DESC
    ) AS rn
  FROM transactions
) t
WHERE rn = 1;

Key points:

• ROW_NUMBER() picks one row per partition; ORDER BY updated_at DESC keeps the latest. Tiebreaker id ensures deterministic choice when timestamps equal.
• The CTE + DELETE is a single-statement, safe operation; run the SELECT version first to validate results before deleting.
  Edge cases:
• NULL updated_at: treated as smallest (comes last when DESC) — if different behavior needed, use COALESCE(updated_at, 'epoch') or define rule.
• Very large tables: run in a transaction off-peak, or delete in batches using the rn CTE with LIMIT and an index on (user_id, transaction_id, updated_at).
  Performance:
• Window function scans the table once; complexity roughly O(n) for the scan plus sorting per partition; ensure appropriate maintenance windows for large datasets.

Direct answer
Diagnosing a dashboard that's become slow, or was fast and suddenly isn't, means working backward through the layers it depends on, pipeline freshness, the query and data model, the semantic layer, and the visualization itself, rather than guessing, since the fix looks completely different depending on which layer is actually the bottleneck.

Structured elaboration
Systematic diagnosis order: first check whether anything upstream actually changed recently (a new column, a new join, a schema change, a data-volume spike), since 'it used to be fast' pointing to a specific recent change is the fastest path to a root cause. Then inspect the query itself: pull the actual query plan the dashboard is generating and look for an unexpectedly expensive operation (a full table scan where an index or partition should have been used, a join that's fanning out more rows than expected, a correlated subquery running once per row instead of once total). Then check the data model layer: is the dashboard querying a large raw fact table directly when a precomputed aggregate would serve it, is there a high-cardinality dimension inflating the result set unnecessarily. Then check the caching layer: is caching actually working as intended, or did a recent change (a new dynamic filter, a parameter that varies per user) accidentally make every query a cache miss. Finally, check the visualization layer itself: sometimes the query is fine and the SLOWNESS is in rendering (an unusually large number of data points being drawn, a chart type that doesn't scale to the row count returned).

Targeted optimizations once you've found the bottleneck: query-level fixes (rewrite an inefficient join, add an index, avoid a correlated subquery); data-model fixes (introduce a precomputed aggregate for this specific query pattern, reduce dimension cardinality with a top-N-plus-other bucketing approach); caching fixes (adjust cache keys so filter combinations that should share a cached result actually do); visualization fixes (aggregate before rendering rather than plotting every raw point).

Validation after a fix: confirm the fix actually addressed the root cause you diagnosed, not just that the dashboard 'feels faster' anecdotally, ideally by measuring the same query's execution time or plan before and after.

Worked example
A key dashboard's load time grows from about 2 seconds to about 20 seconds shortly after a single new chart was added. Working through the layers: nothing upstream changed (data volume and schema are stable), so the issue is likely local to the new chart. Inspecting the new chart's query plan reveals it's joining the main fact table to a dimension table on a non-indexed text column (rather than the indexed integer key the other charts use), causing a full scan on every load instead of an index lookup; separately, this new chart also uses a distinct-count aggregation over a very high-cardinality column, which the existing charts didn't need. The targeted fix: change the join to use the indexed key, and replace the exact distinct-count with a pre-aggregated rollup table maintained for exactly this kind of high-cardinality summary metric (connecting to the precomputation discipline). After the fix, the same query plan shows an index lookup instead of a full scan, and the dashboard's load time returns to roughly 2 seconds, confirming the fix targeted the actual bottleneck rather than a coincidental improvement.

Trade-offs and pitfalls
A common mistake is jumping straight to "add more caching" or "throw a bigger warehouse at it" without first identifying WHICH layer is actually slow, which either wastes effort (caching a query that was never the bottleneck) or masks the real problem temporarily (more compute hides an inefficient query until data volume grows again and the same problem resurfaces, more expensively). The other real pitfall is optimizing based on a single anecdotal 'it feels slow' report without looking at the actual query plan or timing data first; two different users reporting 'the dashboard is slow' can be experiencing two completely different root causes (one hitting an inefficient query, another hitting a rendering issue with a browser-specific data volume), and treating them as the same problem leads to a fix that only solves half the complaints.