Write a SQL query to analyze marketplace operations performance by joining expert activity, user demand, and resolution tables, then calculate daily response time and completion rate by region. Use all demand records, including records without an expert response or resolution. Treat the first response event as the response time and report completion rate as a decimal from 0 to 1.
Output
- One row per demand date and region, including regions with no response.
- Return
region, demand_date, demand_count, responded_count, avg_response_hours, completed_count, completion_rate, and previous_day_completion_rate.
- Sort by
region, then demand_date ascending.
Output details
- Round
avg_response_hours to 2 decimal places and completion_rate to 4 decimal places.
- Return
avg_response_hours as NULL when no demand record has a response.
- Calculate
completion_rate as completed demand records divided by all demand records, yielding 0.0000 when none are completed.
- Count a record as completed only when its resolution status equals
completed.
- Set
previous_day_completion_rate to the preceding available date's rate within each region, or NULL for the first row.
