Data & Analytics

Five business questions answered entirely in SQL against Pagila — a 15-table DVD rental schema with real foreign keys. No pandas, no spreadsheet, no exporting to CSV halfway through.

1. Load it locally. Postgres in Docker, restore the Pagila schema and data, and read the ER diagram before writing a line. Twenty minutes with the schema saves an hour of wrong joins.
2. Answer exactly these five. Revenue by month with the percentage change on the previous month; the top ten customers by lifetime spend with their rental counts; films never rented; each store's most-rented category; and the median days between one customer's rentals.
3. Use the right constructs. Window functions for the running and ranked answers (LAG, RANK, PERCENTILE_CONT), a CTE per logical step, and a LEFT JOIN with a null check for "never rented" rather than NOT IN.
4. Check every join. After each one, compare the row count against the driving table. A join that silently multiplies rows is the most common wrong answer in analytics.
5. Make one query fast. EXPLAIN ANALYZE the slowest, add one index, and record the timing and the plan change side by side.
6. Write the answer, not the query. Two sentences per question saying what the business should conclude. A result set is not an answer.

Scope guard: everything in SQL, including the percentages and the median. The moment you export to a notebook, the exercise is over.

One year of NYC 311 service requests — millions of rows, inconsistent agency names, malformed dates, duplicate complaints and missing boroughs — cleaned in pandas with every decision logged.

1. Take a slice you can iterate on. Filter to one calendar year in the portal, then develop against a 100,000-row sample and run the finished pipeline over the full year once.
2. Profile before touching anything. Per column: dtype, null rate, cardinality, and the ten most frequent values. Write down the five problems you can see; the rest of the project is fixing those five.
3. Fix the dates properly. Parse Created Date and Closed Date, find the rows where closed precedes created, decide what to do with them, and only then compute resolution time.
4. Resolve the entities. Agency and complaint type carry near-duplicates from free-text entry. Build an explicit mapping table — no fuzzy matching without reviewing every pair it proposes.
5. Recover the missing boroughs. Some are recoverable from the coordinates or the incident zip. Report your recovery rate and say what you did with the remainder.
6. Log every decision. One table: column, problem, rows affected, action, reason, and the bias it could introduce. Then restart the kernel, rerun top to bottom, and confirm the counts match.

Scope guard: cleaning only — no modelling, no dashboard. Anyone can call dropna(); explaining why dropping those particular rows did not bias the result is the entire skill.

Three genuinely bad published charts, rebuilt in code — with the reasoning written out, because the critique is the portfolio piece and the redraw is not.

1. Source three real ones. From news articles, annual reports or government releases, not from viz.wtf where the failure is too obvious to be interesting. Save the original image and the underlying numbers.
2. Name the specific fault. Truncated axis, pie chart with eleven slices, dual axes implying a correlation, rainbow scale on ordered data, 3D distorting area. One named fault per chart.
3. Write the question first. State in a sentence what the chart should answer, then choose the form from the FT Visual Vocabulary and say why that form.
4. Rebuild in code. matplotlib, Plotly or D3, reproducible from a published data file rather than drawn in a design tool. Direct labels instead of a legend wherever it fits.
5. Survive the real world. Contrast to WCAG AA, a deuteranopia simulation, and a look at the size it would actually be published — usually 600px wide on a phone.
6. Write the paragraph. Per chart: what the original obscured, what yours reveals, and one thing you deliberately gave up. Every chart trades something away.

Scope guard: three charts done thoroughly, data files published alongside. A redraw with no written critique is decoration.

Nine linked CSVs from the Olist e-commerce set — orders, items, payments, reviews, customers, sellers, products, geolocation and category translations — modelled into a star schema with dbt on Postgres or DuckDB, with tests that fail loudly when the data is wrong.

1. Land raw and untouched. All nine files as-is into a raw schema. No cleaning at this layer — you want to be able to prove what actually arrived.
2. Stage each source once. One staging model per file: consistent naming, explicit types, deduplicated, timestamps in a single timezone. Nothing joined yet.
3. Model the star. fct_orders at one row per order item, with dim_customer, dim_product, dim_seller and dim_date. Declare the grain in the model description — most warehouse bugs are grain bugs.
4. Test the structure. dbt tests: unique and not_null on every key, relationships on every foreign key, accepted_values on order status, and non-negative payment values.
5. Test the business rules. Singular tests for what actually matters: no delivery date before its purchase date, order totals reconciling to items plus freight, and no orphaned order items.
6. Corrupt it on purpose. Duplicate a key, null a foreign key, insert a negative payment. Show the run failing with the test name that caught each one — that output is the deliverable.

Scope guard: one fact table, four dimensions. No orchestrator, no incremental models, no BI tool until the tests catch corruption reliably.

One page a decision-maker acts on without opening the notebook: the recommendation first, the evidence second, the caveats honestly third.

1. Lead with the decision. The first sentence says what they should do and what it costs or returns. Not "this analysis examines" — the recommendation, on line one.
2. Three pieces of evidence, no more. Your strongest three, each a sentence with a number and at most one chart. Everything else goes in an appendix nobody reads, which is fine.
3. Quantify the uncertainty. A range or a confidence statement, plus the one assumption that would flip the recommendation if it were wrong. Name it explicitly.
4. Say what would make you more sure. Which data, how long it would take, and whether it is worth waiting for. You will be asked this every time.
5. Make the ask concrete. The specific next action, who does it, by when. An analysis with no ask is a report.
6. Test comprehension, not approval. Two minutes with a non-technical reader, then ask what you are recommending and what would change your mind. Rewrite until both answers come back right.

Scope guard: one page, no methodology section in the body. If the method needs explaining, the method is the finding and belongs in a different document.