# Analytics Engineering Assignment Pack

Five ready-to-use assignments built around free public resources from
analyticsengineering.com. The datasets are synthetic and contain no personal
information. Instructors may adapt the prompts with attribution.

## 1. Diagnose a SQL join that doubled revenue

Resource: https://www.analyticsengineering.com/sql/joins

### Prompt

Use the visualizer to explain why Ada appears twice after the customer and
order tables are joined. Then write a short query plan for calculating one row
per customer without repeating an order-level or customer-level measure.

### Deliverables

- A declared output grain
- A diagram or paragraph describing the one-to-many relationship
- One query that preserves customers without orders
- One check comparing row counts before and after the join

### Review criteria

- Join type matches the requested population
- Aggregation happens at a compatible grain
- NULL and unmatched records are interpreted correctly

## 2. Investigate a monthly revenue decline

Resource:
https://www.analyticsengineering.com/projects/take-home/revenue-fell-last-month

### Prompt

Complete the public candidate brief using Ecommerce Sales & Returns v1.0.0.
Ask learners not to open the optional instructor reference until they have
saved their own metric definition, validation checks, and executive summary.

### Deliverables

- Metric definition
- Reproducible SQL or notebook
- Three validation checks
- One decision-ready table or chart
- Executive summary of no more than 300 words

### Review criteria

- Payment and refund tables are reduced to order grain before joining
- The comparison follows the supplied reporting-date rule
- Associations are not presented as proven causes

## 3. Build a workforce headcount analysis

Resource:
https://www.analyticsengineering.com/datasets/workforce-employee-attrition

### Prompt

Calculate headcount and recorded exits by department and region. Document what
the current dataset can support and what would require promotion, transfer,
rehire, manager, or assignment history.

### Deliverables

- Declared grain for each result
- Reproducible analysis
- One reconciliation check
- Limitations paragraph

### Review criteria

- Counts use the documented employee and event grains
- Synthetic patterns are not described as workforce benchmarks
- Missing history is stated rather than inferred

## 4. Complete a metric definition

Resource:
https://www.analyticsengineering.com/templates/metric-definition-worksheet

### Prompt

Complete the worksheet for one measure used in an existing report. Include the
population, formula, reporting timestamp, denominator rules, dimensions,
late-arriving behavior, owner, and a worked edge case.

### Deliverables

- Completed worksheet
- One included example and one excluded or differently handled example
- A proposed change-review process

### Review criteria

- Business meaning can be understood without reading SQL
- Grain and time rules are explicit
- Validation protects the most consequential failure mode

## 5. Recover a broken analytical model

Resource:
https://www.analyticsengineering.com/projects/data-forge-the-lost-metrics

### Prompt

Use the project brief to identify where a technically successful model can
still produce the wrong business answer. Prioritize grain, metric definitions,
relationships, and reconciliation before formatting.

### Deliverables

- Model and metric inventory
- Ranked findings with evidence and risk
- Proposed tests
- Recovery sequence and verification plan

### Review criteria

- Findings distinguish query validity from analytical correctness
- Recommendations name the affected grain and downstream consequence
- Verification can prove the correction rather than only rerun the job

## Suggested sequencing

1. Join visualizer
2. Metric worksheet
3. Revenue take-home or workforce analysis
4. dbt debugging project

Learners should attempt each brief before viewing completed instructor examples.