Sorry, but I never grow tired of these. We wanted to know the average duration between an online shopper creating their cart and when they actually checked out.
Analytic functions make this a snap.
select min(gap), bucket
from (select submitted_date - creation_date gap,
ntile(20) over (order by submitted_date - creation_date) as bucket
from dcspp_order
where submitted_date is not null
)
group by bucket
order by 2
/
This provided 20 buckets with the minimum amount of time between a cart being created and when it was checked out. Through this, we could see that 14 out of 20 buckets checked out in less than an hour.
Coolness.