C04AdvancedCTE · range join

Post-release incident count

For each succeeded deployment, count incidents for the same customer opened from its deployed_at inclusive to 7 days later exclusive. Return deployment_id, customer_id, and incidents_7d; keep only nonzero counts and sort highest first, then deployment_id.

Data available

Use these tables

2 tables · 1 relationship

Table 1

deployments

Customer releases, including failures and rollbacks.

deployment_idintegerPK
customer_idintegerFK
engineer_idintegerFK
versiontext
statustext
deployed_attimestamptz

Table 2

incidents

Operational incidents; unresolved rows have no resolved_at.

incident_idintegerPK
customer_idintegerFK
deployment_idintegerNULLFK
severitytext
opened_attimestamptz
resolved_attimestamptzNULL
root_causetextNULL

How the tables connect

incidents.deployment_id=deployments.deployment_id

Query editor

PostgreSQL

⌘↵ Run query

Result stream

Output

Run your query to inspect its result.

Contextual coach

Ask about this attempt

Run or check a query first. The coach only comments on a real error or grader mismatch.

Field notes

Hints & explanation

Try to solve it cold. Two progressive hints are available.