Shopify Data Engineer SQL Pair Programming: Practice Questions and Preparation

Shopify data engineer SQL pair programming preparation should start with a query you can explain, run, and challenge. A correct-looking aggregate is not enough if a join duplicates orders or a rolling average silently skips dates. Practice making those assumptions visible while another person follows your work.

Start with PracHub's rolling revenue question with missing dates. Then use the original commerce exercise below to rehearse a fuller conversation: define the population, inspect the inputs, build the query in stages, and verify a deliberately broken version.

Evidence boundary: Official Shopify material describes engineering culture. Candidate posts provide limited, variable interview context. This article's orders, fulfillment records, SQL, expected outputs, and pairing dialogue are original preparation material, not a Shopify assessment or a prediction of your questions.

What the available evidence says about the SQL round

Official context: Shopify's Engineering and Data careers page discusses pair programming and extensive everyday AI use. It supports preparing to communicate technical reasoning. It does not specify a universal data engineer interview platform, SQL dialect, question count, or policy allowing external AI during every assessment. Shopify Engineering and Data

Candidate reporting: A July 2026 InterviewDB post relays a senior data engineer's account involving runnable SQL and date-based aggregation. It is a community repost, not an independently authenticated hiring specification. A separate Reddit preparation request asks about a SQL-and-AI pairing invitation; asking for advice is not evidence of a completed interview. Reported SQL experience, preparation request

These sources do not establish two independent, completed, same-cycle accounts covering all roles. The practical response is to prepare the underlying SQL skills while confirming your own invitation's requirements. Do not import a SWE robot exercise, a data science case, or an old internship sequence into a data engineer SQL session.

Confirm the invitation before choosing tools

Establish which role and level the session concerns, what SQL engine you will use, whether queries can be executed, and what external resources are permitted. Also check whether the interviewer expects screen sharing, a provided editor, or a local setup. Those details affect how you rehearse; none should be inferred from Shopify's everyday engineering culture.

If AI is expressly permitted, practice reviewing its output: identify the grain, explain each join, run a counterexample, and correct the result yourself. If the policy is unclear, prepare without external assistance and ask the recruiting contact before the assessment. Keep preparation material separate from help during an active interview.

A useful personal drill is to solve the same small problem with and without autocomplete. You should still be able to explain why the answer is correct when a tool suggestion is removed. Memorizing a generated query gives you little help when the interviewer changes the denominator.

Define the original commerce task

Imagine two shops, A and B, observed from August 1 through August 7, 2026. Produce one row per shop per calendar day, including days with no paid orders. Report paid order count, gross order value, and how many of those orders are fully shipped by the extract cutoff. Then calculate the previous three calendar days' average order count, excluding the current day.

The three-day window is deliberately short enough to check by hand. It is an original exercise choice, not a reported Shopify requirement. Require a complete three-day history; return NULL for earlier rows instead of averaging a shorter period without disclosure.

Treat the fulfillment extract as complete through August 7. Its rows represent shipment events, and an order can have multiple events. The fully-shipped measure groups orders by their order date but evaluates their final status at the extract cutoff. It does not describe what the business knew on each historical day.

That distinction prevents a subtle interpretation error. To answer “How many were fully shipped as of that day?” you would need fulfillment event timestamps and a historical cutoff in the query. This fixture deliberately omits those timestamps, so it cannot support that different question.

Load all three tables and inspect their grain

Run the following original fixture in SQLite. Order IDs are unique, but fulfillment event IDs can repeat because of ingestion retries. All values are synthetic USD; units are positive integers, and currency conversion is outside the exercise.

CREATE TABLE orders(id TEXT PRIMARY KEY, shop TEXT, day TEXT, status TEXT, units INT, gross INT);
INSERT INTO orders VALUES
 ('O1','A','2026-08-01','paid',2,100),
 ('O2','A','2026-08-01','paid',1,40),
 ('O3','A','2026-08-03','paid',1,60),
 ('O4','A','2026-08-04','cancelled',1,80),
 ('O5','B','2026-08-02','paid',3,90),
 ('O6','B','2026-08-04','paid',1,50);
CREATE TABLE fulfillments(event TEXT, order_id TEXT, units INT);
INSERT INTO fulfillments VALUES
 ('F1','O1',1),('F2','O1',1),('F3','O3',1),
 ('F4','O5',1),('F5','O6',1),('F1','O1',1),('F99','OX',1);
CREATE TABLE calendar(day TEXT PRIMARY KEY);
INSERT INTO calendar VALUES
 ('2026-08-01'),('2026-08-02'),('2026-08-03'),
 ('2026-08-04'),('2026-08-05'),('2026-08-06'),('2026-08-07');

The inputs contain six orders, seven fulfillment rows, and seven calendar dates. O4 is cancelled and must not enter paid-order metrics. F1 appears twice with identical attributes; keep one copy. F99 points to an absent order, so report it as an attribution exception rather than inventing an order to receive it.

Do not deduplicate fulfillment rows by order ID. F1 and F2 are different valid shipment events for O1; together they ship its two units. Conversely, an event ID repeated with conflicting amounts would require a conflict policy. DISTINCT across all columns resolves this fixture's exact retry, not every production duplicate.

Before aggregating, state the checks aloud: six unique order IDs, one exact duplicate event, one orphan event, and five eligible orders. Those checks turn a hidden assumption into a testable data contract. You can then ask whether the interviewer wants exceptions excluded, reported separately, or treated as a blocking quality issue.

Preserve order value when joining fulfillments

A tempting query joins orders directly to deduplicated shipment rows and sums gross value. That still counts O1 twice because it has two legitimate shipment events. Removing the retry is necessary, but it does not make the relationship one-to-one.

In this fixture, that incorrect join produces $440 instead of $340 for paid orders. O1's $100 is counted twice. Using SUM(DISTINCT gross) is not a general repair: two unrelated orders may have the same value, and both must count.

Aggregate shipment units to one row per order first. Then left join that result to paid orders. Preserve unshipped O2 with zero known shipped units because the extract is assumed complete. If the source were incomplete, absence might mean unknown rather than zero; the data contract would need to change.

Fully shipped means shipped units are at least ordered units in this exercise. A production check should also flag overshipment rather than allowing the threshold to hide it. No such case appears in these inputs, so describe it as a follow-up, not a defect supposedly detected in the fixture.

Build a date spine before applying the window

The full query below creates an order-grain stage, aggregates by shop and day, and joins those metrics onto every required shop-date combination. Only then does it apply the window.

WITH clean_events AS (
 SELECT DISTINCT event,order_id,units FROM fulfillments
), shipped AS (
 SELECT order_id,SUM(units) AS units FROM clean_events GROUP BY order_id
), order_grain AS (
 SELECT o.*,COALESCE(s.units,0) AS shipped_units
 FROM orders o LEFT JOIN shipped s ON s.order_id=o.id
 WHERE o.status='paid'
), daily AS (
 SELECT shop,day,COUNT(*) AS orders,SUM(gross) AS gross,
 SUM(CASE WHEN shipped_units>=units THEN 1 ELSE 0 END) AS fully_shipped
 FROM order_grain GROUP BY shop,day
), spine AS (
 SELECT s.shop,c.day,COALESCE(d.orders,0) AS orders,
 COALESCE(d.gross,0) AS gross,COALESCE(d.fully_shipped,0) AS fully_shipped
 FROM (SELECT DISTINCT shop FROM orders) s CROSS JOIN calendar c
 LEFT JOIN daily d ON d.shop=s.shop AND d.day=c.day
), windows AS (
 SELECT *,COUNT(*) OVER w AS prior_days,AVG(orders) OVER w AS prior_average
 FROM spine
 WINDOW w AS (PARTITION BY shop ORDER BY day ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING)
)
SELECT shop,day,orders,gross,fully_shipped,
 CASE WHEN prior_days=3 THEN ROUND(prior_average,3) END AS prior_3_day_average
FROM windows ORDER BY shop,day;

The cross join here produces two shops times seven dates: fourteen rows. It is intentional and bounded. On a real merchant dataset, choose the eligible shop population and date range before generating a spine; an uncontrolled cross join can create an unnecessarily large intermediate result.

The shop list comes from orders only because both eligible shops are represented there. If shops with no orders anywhere in the period must appear, use an authoritative shop dimension. Deriving the population from activity would otherwise exclude exactly the zero-activity shops you need to measure.

Technical reference: SQLite documents explicit window frames and partitioning. A ROWS frame counts ordered rows, not elapsed days. After creating exactly one row per shop-date, three preceding rows correspond to the intended three calendar days. That equivalence would fail on the sparse order-date aggregate. SQLite window functions

Check these outputs before discussing performance

The complete query was executed. These selected rows expose the important boundaries; the full result contains fourteen rows. Dates below are in August 2026.

For A on August 4, the earlier counts are 2, 0, and 1, giving an average of 1. For B on August 5, they are 1, 0, and 1, giving two-thirds. Rounding occurs only for display; the calculation uses the unrounded average.

Across all result rows, order counts sum to five and gross values sum to $340. Three eligible orders are fully shipped: O1, O3, and O6. O5 is only partly shipped, and O2 has none. Verify those identities as well as the totals; two opposing errors can cancel in an aggregate.

The first three dates for each shop have NULL prior averages. Zero would mean a complete window containing no orders. NULL means the requested complete window is unavailable. Explain that difference when presenting an empty cell to an interviewer or stakeholder.

Rehearse a pairing conversation that exposes the bug

Use the following original dialogue as a rehearsal structure, not a script to recite. The purpose is to make your decisions easy to inspect.

Clarify: “Are we measuring paid orders by order date, with fulfillment status as of the extract cutoff? I will exclude cancelled orders and preserve zero-order days.” This establishes population, time meaning, and output grain before syntax takes over.

Explain a checkpoint: “There are five eligible orders. Before adding shipment data, their values total $340. I expect a one-to-one enrichment to preserve both controls.” A small invariant tells the other person what you will check next.

Debug openly: “My joined sum is $440, so the join has changed the order grain. O1 has two distinct shipments. I will aggregate units by order before joining.” This is more useful than repeatedly changing the aggregate until a number looks plausible.

Defend the window: “The metric needs calendar days, so I generated the date spine first. I excluded the current row and require three previous dates.” If the interviewer changes the requirement to include today, describe both the frame change and the expected output change.

Finish by explaining one limitation and one additional test. For example, a new shop with no orders would require a shop dimension. That shows you understand where the solution stops without turning a small exercise into a speculative production architecture.

Practice five related PracHub questions

These are selected SQL and data reasoning exercises, including adjacent analytical roles. They are not a verified Shopify data engineer question bank or a promise about your next interview.

Return to the missing-date revenue exercise and deliberately remove a date from the input. Explain which window result changes, then repair it while keeping the output grain explicit.

Sources and Further Reading

• Shopify: Engineering and Data — official work-culture context, not a universal interview policy.
• InterviewDB: reported senior data engineer SQL experience — community repost with unverified independence.
• Reddit: SQL and AI pair programming preparation request — a request for advice, not a completed-loop report.
• SQLite: Window functions — primary technical reference.