1. Перший запит для воронки

select 
  funnel_step,
  funnel_name,
  platform,
  age_range,
  sum (number_of_users) as sum_number_of_users,
  sum (number_of_rides) as sum_number_of_rides
from funnel_analysis
group by 
	funnel_step,
	funnel_name,
	platform,
	age_range
order by funnel_step asc;

Отримали таблицю для побудови воронки, та перший csv файл для аналізу.

1. Запити для побудови PDF та CDF (в Excel)

select 
	rr.ride_id,
	extract(EPOCH from (accept_ts - request_ts)) / 60 as request_to_accept_time_minutes,
	extract(EPOCH from (cancel_ts - accept_ts)) / 60 as accept_to_cancel_time_minutes,
	to_char(rr.request_ts, 'Day') as weekday,
	extract(hour from rr.request_ts) as request_hour
from ride_requests rr
where extract(EPOCH from (cancel_ts - accept_ts)) is not null;

select 
	rr.ride_id,
	extract(EPOCH from (accept_ts - request_ts)) / 60 as request_to_accept_time_minutes,
	to_char(rr.request_ts, 'Day') as weekday,
	extract(hour from rr.request_ts) as request_hour
from ride_requests rr
where extract(EPOCH from (accept_ts - request_ts)) is not null;

select 
	rr.ride_id,
	extract(EPOCH from (pickup_ts - request_ts)) / 60 as request_to_pickup_time_minutes,
	to_char(rr.request_ts, 'Day') as weekday,
	extract(hour from rr.request_ts) as request_hour
from ride_requests rr
where extract(EPOCH from (pickup_ts - request_ts)) is not null;

1. Загальна таблиця ride_requests. Другий csv файл для аналізу.

select 
	rr.ride_id,
	r.rating,
	rr.user_id,
	s.age_range,
	ad.platform,
	rr.driver_id,
	ad.download_ts,
	s.signup_ts,
	rr.request_ts,
	rr.accept_ts,
	rr.pickup_ts,
	rr.dropoff_ts,
	rr.cancel_ts,
	t.purchase_amount_usd,
	t.charge_status,
	t.transaction_ts,
	extract(EPOCH from (signup_ts - download_ts)) / 60 as download_to_signup_time_minutes,
	round(extract(EPOCH from (request_ts - signup_ts)) / 60, 0) as signup_to_request_time_minutes,
	extract(EPOCH from (accept_ts - request_ts)) / 60 as request_to_accept_time_minutes,
	extract(EPOCH from (cancel_ts - accept_ts)) / 60 as accept_to_cancel_time_minutes,
	extract(EPOCH from (pickup_ts - accept_ts)) / 60 as accept_to_pickup_time_minutes,
	extract(EPOCH from (pickup_ts - request_ts)) / 60 as request_to_pickup_time_minutes,
	to_char(rr.request_ts, 'Day') as weekday,
	extract(hour from rr.request_ts) as request_hour
from ride_requests rr
	left join transactions t on rr.ride_id = t.ride_id
	left join signups s on rr.user_id = s.user_id
	left join app_downloads ad on s.session_id = ad.app_download_key
	left join reviews r on rr.ride_id = r.ride_id;

1. Розглянемо чи є залежність від часу доби. Обчислимо середню кількість запитів, підтверджень та скасувань погодинно, попередньо обчисливши загальну кількість запитів, підтверджень та скасувань по годинах і днях.

select 
    extract(hour from request_ts) as request_hour, 
    round(avg(total_requests), 2) as avg_requests_per_hour,
    round(avg(accepted_requests), 2) as avg_accepted_per_hour,
    round(avg(cancelled_requests), 2) as avg_cancelled_per_hour,
    round(100.0 * avg(cancelled_requests) / avg(accepted_requests), 2) as cancel_rate_from_accepted,
    round(100.0 * avg(cancelled_requests) / avg(total_requests), 2) as cancel_rate_from_requested
from (
    select 
        request_ts,
        count(*) over (
        	partition by date(request_ts), 
        	extract(hour from request_ts)
        ) as total_requests,
        count(accept_ts) over (
        	partition by date(request_ts), 
        	extract(hour from request_ts)
        ) AS accepted_requests,
        count(cancel_ts) over (
        	partition by date(request_ts), 
        	extract(hour from request_ts)
        ) as cancelled_requests
    from ride_requests
) subquery
group by request_hour
order by request_hour;

Розподіл кількості по годинам

select
    extract(hour from request_ts) as request_hour, 
    count(*) as total_requests,
    count(cancel_ts) as cancelled_requests,
    round(100.0 * count(cancel_ts) / count(*), 2) as cancel_rate,
    count(accept_ts) as accept_requests,
    count(pickup_ts) as pickup_requests,
    count(dropoff_ts) as dropoff_requests
from ride_requests
group by request_hour
order by request_hour;