For a Shopify data-science role, connect the analysis to a merchant decision. A query can be syntactically correct and still answer the wrong question if it drops merchants with no orders. An experiment can show a positive point estimate and still have too much uncertainty for the decision you want to make.

Use the Data Scientist practice collection for SQL and experimentation drills, then attempt the merchant activation case below without reading its solution.

05Prepare the work; confirm the interview format

Shopify’s current careers page identifies data-science work, but the 8 October 2026 check did not establish a fixed ten-stage sequence or guarantee a named Life Story round for every data-science candidate. Ask your recruiting contact which assessments apply, which tools and languages are allowed, and whether a career-story or project discussion is included. Shopify careers.

These are useful preparation areas rather than a confirmed round map:

Avoid studying every possible assessment as if it were scheduled. Confirm the actual format, then use the case to exercise the reasoning that carries across several of them.

06A complete merchant-activation experiment

This is an original practice case, not a reported Shopify interview. A product team proposes a simpler onboarding flow. The primary metric is the share of eligible newly signed-up merchants with at least one paid order within seven days of assignment. Randomize by merchant, keep that assignment stable and count all assigned eligible merchants in their original groups. A buyer-level analysis would answer a different question and could understate dependence between orders from the same merchant.

Control has 10,000 merchants and 500 activate; treatment has 10,000 and 560 activate. The observed rates are 5.0% and 5.6%. The absolute increase is 0.6 percentage points; the relative increase is 12%. State both units. Using an illustrative independent-merchant normal approximation, the standard error of the difference is about 0.317 percentage points. The approximate 95% interval is −0.02 to +1.22 percentage points. It crosses zero; this fixture does not establish a reliable positive effect by that criterion.

Do not change the stopping rule after seeing that interval. Before running the experiment, choose the minimum effect worth shipping, power, duration and analysis plan. Wait for every included cohort’s seven-day outcome window to mature. Check sample-ratio mismatch, assignment stability, missing outcomes and instrumentation before interpreting a treatment effect. Repeated daily peeking needs an appropriate sequential method rather than repeated fixed-horizon significance tests.

The recommendation is to continue according to the preregistered plan, provided guardrails remain acceptable. Track refunds, support contacts, checkout errors and revenue per assigned merchant. A conversion gain achieved by low-value or fraudulent orders may not meet the business goal. Look at segments defined before analysis; searching for one winning slice afterward raises the chance of a misleading result.

Follow-up: Mobile merchants appear to gain 2 percentage points while desktop merchants lose 1. Ask for subgroup sizes, uncertainty and a pre-specified interaction analysis. Verify that mobile assignment and checkout tracking have the same failure rates across groups. Do not announce a mobile-only rollout from two point estimates. Describe a follow-up experiment if the evidence warrants it.

07SQL with a denominator and a seven-day boundary you can test

The small fixture below tests query correctness, separately from the 20,000-merchant experiment above. Timestamps are normalized UTC strings. The dialect is SQLite, chosen so this example runs without a database service. In PostgreSQL, replace datetime(assigned_at, '+7 days') with a timestamp plus INTERVAL '7 days' and use appropriate timestamp types.

CREATE TABLE assignment(merchant_id INTEGER PRIMARY KEY,
                        variant TEXT, assigned_at TEXT);
CREATE TABLE orders(order_id INTEGER PRIMARY KEY,
                    merchant_id INTEGER, paid_at TEXT, revenue INTEGER);
INSERT INTO assignment VALUES
 (1,'control','2026-01-01 00:00:00'),(2,'control','2026-01-01 00:00:00'),
 (3,'treatment','2026-01-01 00:00:00'),(4,'treatment','2026-01-01 00:00:00');
INSERT INTO orders VALUES
 (11,1,'2026-01-01 00:00:00',100),(12,1,'2026-01-02 00:00:00',50),
 (13,2,'2025-12-31 23:59:59',900),(14,3,'2026-01-03 00:00:00',80),
 (15,4,'2026-01-07 23:59:59',70),(16,4,'2026-01-08 00:00:00',900);

WITH merchant_outcome AS (
 SELECT a.merchant_id, a.variant,
        CASE WHEN COUNT(o.order_id)>0 THEN 1 ELSE 0 END AS activated,
        COALESCE(SUM(o.revenue),0) AS revenue
 FROM assignment a LEFT JOIN orders o
   ON o.merchant_id=a.merchant_id
  AND o.paid_at>=a.assigned_at
  AND o.paid_at<datetime(a.assigned_at,'+7 days')
 GROUP BY a.merchant_id,a.variant
)
SELECT variant, COUNT(*) AS assigned, SUM(activated) AS activated,
       1.0*SUM(activated)/COUNT(*) AS activation_rate,
       1.0*SUM(revenue)/COUNT(*) AS revenue_per_assigned
FROM merchant_outcome GROUP BY variant ORDER BY variant;

Expected output: control has 2 assigned, 1 activated, a 0.5 activation rate and revenue per assigned merchant of 75; treatment has 2 assigned, 2 activated, a 1.0 activation rate and the same revenue per assigned merchant of 75. Order 13 is before assignment and order 16 is exactly at the exclusive seven-day boundary; neither counts. Merchant 1’s two orders must not double the denominator.

Keep time predicates inside the left join. Moving them into WHERE can remove merchants with no orders and inflate activation. Confirm one assignment per merchant and deduplicate payment events into a trustworthy order fact before this query. A primary key prevents duplicate order IDs in this fixture; it does not prove a production event stream has already been reconciled.

For a 40-minute mock, spend 10 minutes defining the decision and unit, 10 interpreting the experiment, 15 writing/running this query and 5 defending the left join and boundary. Keep the expected rows beside your result. Review denominator preservation, absolute/relative units, matured cohorts, uncertainty and guardrails. These are editorial checks, not Shopify’s internal rubric.

08Debug the analysis when the query runs but the answer looks wrong

Start with the grain. The assignment table contains one row per merchant; the order table can contain several. A direct join followed by COUNT(*) counts joined rows, not assigned merchants. Merchant 1 has two qualifying orders in the fixture, so that approach changes the denominator. Aggregate the outcome at merchant grain before grouping by variant.

Next inspect the time boundary. An order before assignment is not an outcome of the assigned onboarding flow. An order exactly seven days later is outside this exercise’s half-open window. Keeping the timestamp representation normalized matters because the fixture compares UTC strings in a consistent sortable format; mixed offsets or inconsistent formatting would need proper normalization and types.

Then inspect missing outcomes. Merchant 2 has no qualifying order, but remains an assigned merchant with activated=0 and revenue=0. Filtering joined order fields in WHERE can eliminate that row. A result with an unexpectedly high activation rate is a reason to compare the assigned population before and after each transformation.

09Revenue can change the decision without changing activation

The tiny query fixture has equal revenue per assigned merchant despite different activation rates. That illustrates why the business objective matters. More merchants placing at least one order does not automatically mean higher revenue or lower support cost. For a real experiment, define the primary decision metric and guardrails before looking at the data.

Revenue is often skewed and delayed. The simple independent-proportion interval applies to the activation indicator under the stated assumptions; it is not a revenue confidence interval. A revenue analysis needs an appropriate method, treatment of outliers and a mature observation window. Do not copy the activation standard error into a differently distributed metric.

In a follow-up, suppose support contacts rise while activation rises. Ask for the magnitude, denominator, affected segments and whether the support burden was an agreed guardrail. Separate a transient onboarding question from a serious checkout defect. Then explain whether you would continue, pause or narrow the experiment and what evidence you need for that decision.

An interview answer becomes stronger when it retains the link from merchant outcome to data grain, analysis and action. A correct formula alone does not choose the product decision, and a persuasive recommendation cannot repair an incorrect denominator.

10A project explanation that survives follow-ups

Choose one real analysis that changed a product or operational decision. Start with the decision and affected users, then explain the data source and grain. State what you personally did and what engineers, analysts or product partners contributed. If you describe an experiment, explain assignment, exposure, exclusions, sample size and uncertainty. If it was observational, do not call the association a randomized treatment effect.

Expect follow-ups about missing events, selection bias, novelty effects and delayed outcomes. A credible answer can include “we could not establish causality with that data.” Explain the next check you proposed rather than pretending every project ended with a clean uplift.

11A two-week preparation route

In week one, use four sessions: reproduce the SQLite fixture, change its time boundaries, analyze the activation experiment and explain one real project. Write a defect log after each session. A query that quietly excludes zero-order merchants belongs in that log even if its result looks plausible.

In week two, add a merchant segmentation question, a sample-ratio check, a case with delayed revenue and a full mock. For each case, finish with the decision, assumptions and next measurement. On the final day, repeat the weakest case with a new constraint instead of memorizing the answer you first gave.

Do I need to know every machine-learning algorithm?

Match preparation to the current role. Product analytics, experimentation and ML-focused positions can require different depth. Ask where the team expects you to spend your time and prepare one relevant end-to-end project.

Should I ship because the treatment rate is higher?

Not from the point estimate alone. In the supplied experiment, the approximate interval crosses zero. Consider the decision cost, pre-agreed analysis plan and guardrails. “Not yet enough evidence for this decision” is different from proving there is no effect.

What is the most important SQL habit in this case?

Keep the assigned population in the denominator. The LEFT JOIN and its window predicates in ON preserve merchants without qualifying orders. Moving those predicates into a WHERE clause can change the question.

12Sources and Further Reading

• Shopify careers
• SQLite date and time functions
• SQLite SELECT and join behavior

Editorial revision: 8 October 2026. Worked fixtures and practice rubrics are original PracHub material. Current hiring details remain role-specific; unavailable source pages and unverified timings are identified in the text.