1. Learn
    2. /
  2. Courses
    3. /
  3. Exploratory Data Analysis in SQL

Exercise

Rats!

Requests in category "Rodents- Rats" average over 64 days to resolve. Why?

Investigate in 4 steps:

  1. Why is the average so high? Check the distribution of completion times. Hint: date_trunc() can be used on intervals.

  2. See how excluding outliers influences average completion times.

  3. Do requests made in busy months take longer to complete? Check the correlation between the average completion time and requests per month.

  4. Compare the number of requests created per month to the number completed.

Remember: the time to resolve, or completion time, is date_completed - date_created.

Instructions 1/4

undefined XP
  • 1
    • Use date_trunc() to examine the distribution of rat request completion times by number of days.
  • 2
    • Compute average completion time per category excluding the longest 5% of requests (outliers).
  • 3
    • Get corr() between avg. completion time and monthly requests. EXTRACT(epoch FROM interval) returns seconds in interval.
  • 4
    • Select the number of requests created and number of requests completed per month.