/*

Table: trips                             
+-----------------+-----------+
| Field           | Data Type |
+-----------------+-----------+
| trip_id         | Integer   |
+-----------------+-----------+
| trip_timestamp  | Timestamp |
+-----------------+-----------+
| user_id         | Integer   |
+-----------------+-----------+
| revenue_usd     | Float     |
+-----------------+-----------+
| trip_distance_m | Float     |
+-----------------+-----------+
| trip_time_s     | Integer   |
+-----------------+-----------+
| market_id       | Integer   |
+-----------------+-----------+
  
  
Table: users                  
+-----------------+----------------------+
| Field           | Data Type            |
+-----------------+----------------------+
| user_id         | Integer              |
+-----------------+----------------------+
| user_segment    | Enum {top, mid, low} |
+-----------------+----------------------+
| date_joined     | Date                 |
+-----------------+----------------------+


Table: markets
+-----------------+---------------------+
| Field           | Data Type           |
+-----------------+---------------------+
| market_id       | Integer             |
+-----------------+---------------------+
| market_name     | String              |
+-----------------+---------------------+
| market_type     | Enum {campus, city} |
+-----------------+---------------------+

*/

/* Q1

Question: Write a query that returns all trips in our San Francisco and Los Angeles markets. Output should be: trip_id, trip_timestamp, market_name.
*/

select
  trip_id,
  trip_timestamp,
  market_name
from trips t
join markets m
on t.market_id = m.market_id
where market_name in ('Los Angeles', 'san Francisco')


/* Q2

Question: Write a query that returns the total number of trips and average trip distance broken down by user segment. Output should be: user_segment, “number of trips”, “average trip distance” 

*/  
select
  user_segment,
  count(t.*) as 'number of trips',
  avg(trip_distance_m) as 'average trip distance'

from users u

join trips t
on u.user_id = t.user_id

group by
  user_segment



/* Q3

Question: Write a query that returns all markets where more than 5000 trips have been taken by “top” users. Output should be: market_name, “number of trips” 

*/

select
  market_name,
  count(trip_id) as 'number of trips'
from trips t 

join users u
on t.user_id = u.user_id

join market m
on t.market_id = u.user_id

where user_segment = 'top'

group by
  market_name

having count(trip_id) > 5000

/* Q4

Question: Write a query that returns the total number of trips taken in each market. Be sure to include a 0 for markets where no trips have been taken. Output should be: market_name, “number of trips”

*/

select
  market_name,
  count(t.trip_id) as 'number of trips'
  
from market m
left join trips t
on m.market_id = t.market_id

group by
  market_name

/* Q5

Question: Write a query that has all trips a user has taken, along with the total amount they have spent on trips prior to and including that trip. Output should be: trip_id, user_id, trip_timestamp, “total running revenue”

*/

select
  trip_id,
  user_id,
  trip_timestamp,
  total_sum as 'total running revenue'
from
(
  select
      trip_id,
      user_id,
      trip_timestamp,
      sum(revenue_usd) over (partition by user_id order by trip_timestamp) as total_sum,
      row_number() over (partition by user_id order by trip_timestamp desc) as rnum
  from trips
) total_paid

where rnum = 1 

order by
  user_id

/* Q6

Question: Write a query that returns the total number of trips every user has taken in every market. For markets where a user has not taken a trip, the query should return 0. 

market_name, user_id, total_trips

*/

select
  user_id,
  market_name,
  count(user_id) over (partition by market_name) as 'total trips'
from market m

left join users u
on u.market_id = m.market_id

group by
  user_id,
  market_name

/*  


************************* Case Study Question ************************* 

A goal for Spin this quarter is to improve our trip conversion rate, which is measured as the total number of trips over the total number app sessions. As an analyst, your task is to help us better understand our conversion rate and make suggestions about how we can improve this metric. 

-baseline conversion rate: 75%
-target: 2% improvement --> 77%

- take app opens (last two weeks), trips taken (last two weeks)