Homework 4

MSBA 402 – Prof. Rosario

It is important to follow these conventions. Most of them are adopted in industry. Also, it is easy
to develop a reliance on certain constructs in SQL and we must be able to assess that you are
capable of using all of the taught constructs of the SQL standard. They include but are not
limited to:

1. Always use the SQL standard, but know that functions applied to certain data types
(numeric and date/time) tend to be implementation (MySQL) specific.
2. Unless otherwise noted, all queries are SELECTs and should not change the database.
3. Always explicitly specify your join conditions. Do not use USING, NATURAL JOIN or
multiple tables in FROM or anything similar.
4. Use a subquery if necessary and limit it to one level.
5. Do not use numerical indices in GROUP BY, ORDER BY
6. In aggregations, the group keys come first in the SELECT and the aggregations come
last.
7. Do not use SELECT *
8. When filtering on aggregate results, use HAVING rather than moving the aggregation
into a subquery.
9. You may use CTEs but do not use temporary tables.

Bike Share

In the class database, we have four tables that contain about one day of data from the San
Francisco Bike Share project. The original data contains about 2.5 years of data and can be
found here. The data was heavily processed for this homework assignment. As a fun exercise,
you may wish to rerun these queries on the full data.

Context: The bike share works as follows:

 Bikes are placed strategically through the City of San Francisco.

 Users unlock the bike, ride it around and then park it at another bike share station.
These are physical "parking lots" and the bikes cannot simply be left anywhere.
 The user pays a fee to unlock the bike (start a ride) and then a fee for the amount of time
the bike was ridden.
 This is very similar to how Bird Scooter and other services work.

The data are as follows:

 trip_start contains information about the start of each bike trip including the start
time, ride origin and the specific bike used for the ride. Note that the trip_start table
 contains a column cust_id that is the foreign key to the customer table's primary key,
id;
 trip_end contains information about the end of each bike trip including the end time,
ride destination;
 customer contains information about the user that initiated each trip, namely what type
of user they are.
 station contains information about each station in the system including GPS location.

You will want to look at this page for date/time functions that may be useful and this page for
numeric functions. Note that there is no SQL standard way of performing arithmetic on
timestamps.

Exercises

Important: None of these exercises require a self join.

1. (3 points) Do not use AI. Write a query that identifies stations where round trips (trips
that start and end at the same station) occur. For each station that has at least 2 round
trips, return the station name and the total count of round trips. (These are sometimes
called "joy rides" and are not the intended use of the bike share system.) Your query
should return station_name and round_trip_count, sorted by round_trip_count
descending, then station_id ascending. If you use a join, it must be of the appropriate
type based on the problem requirements.

Ans:
SELECT staion_name ,
round_trip_count
FROM ( SELECT
[Link] ,
[Link] as staion_name ,
COUNT([Link] ) as round_trip_count
FROM trip_start start INNER JOIN trip_end end
ON [Link] = [Link]
INNER JOIN station st
ON start.station_id = [Link]
WHERE start.station_id = end.station_id
GROUP BY start.station_id
HAVING COUNT([Link] ) >= 2
ORDER BY round_trip_count DESC , start.station_id ASC ) as subq1

2. (3 points) Write a query that computes the elapsed time of each trip in minutes. In the
case of fractional minutes, round up to the next whole number. For example, if a trip
duration is computed as 4.02 minutes, your query should return 5 minutes for that trip.
Assume timestamps may include seconds (e.g., 14:23:47), not just whole minutes.

Some trips may have no end time (NULL). Return these trips in your results with NULL
as the length - do not filter them out or compute a special value.

Your query should return id renamed to trip_id, and the trip length named as
 length, and only these two columns. The results should be sorted in ascending order
by the trip_id. Your method must use a join. To round properly, you will want to use a
function that computes f(x) =⌈x⌉ . Do not use a subquery. If you use AI to help you,
you must explain what each line of the query is doing.

Hint: trip 213462 has length 14 minutes, 214204 is NULL.

SELECT [Link] as trip_id ,

CEIL(TIMESTAMPDIFF(SECOND , [Link] , [Link])/60) as length
FROM trip_start ts LEFT JOIN trip_end ed
ON [Link] = [Link]
ORDER BY [Link] ;

3. (4 points) Do not use AI. Modify the query you wrote for part (2) to add another column
to the output that computes the charge to the user for each trip. For this problem, we
assume all users are the same. The charge is calculated as follows:

- $3.99 to unlock the bike (start the ride)

- $0.30 for each minute (minutes were computed in part (2))

If the ride never ended, we assume the bike was stolen and instead charge the user
$1000. You must write a full query – that is, do not assume that we stored the result
from (2) anywhere. Your query must output the columns trip_id and charge in ascending
order by trip_id. While it may seem tempting to use a subquery, see if you can write it
without one.

SELECT [Link] as trip_id ,

CASE WHEN [Link] IS NULL THEN 1000
ELSE 3.99 + 0.30 * (CEIL(TIMESTAMPDIFF(SECOND , [Link] ,
[Link])/60))
END as charge
FROM trip_start ts LEFT JOIN trip_end ed
ON [Link] = [Link]
ORDER BY [Link] ASC
4. (5 points) The table customer contains the user type associated with each trip. The
user type may be Subscriber or Customer. Subscribers pay a monthly fee to save a bit
on each trip. On the other hand, Customers pay a higher rate. Modify your query from
part (3) to adhere to the following pricing logic, while also charging the user $1000 if the
trip does not end. The pricing logic is as follows:

Subscribers are charged $0.15 for each minute. There is no unlock fee.
Customers are charged as before: $3.99 to unlock then $0.30 per minute.
SELECT [Link] as trip_id ,
CASE WHEN [Link] IS NULL THEN 1000
WHEN [Link] = 'Subscriber' THEN 0.15 * (CEIL(TIMESTAMPDIFF(SECOND
, [Link] , [Link])/60))
WHEN [Link] = 'Customer' THEN 3.99 + 0.30 *
(CEIL(TIMESTAMPDIFF(SECOND , [Link] , [Link])/60))
END as charge
FROM trip_start ts LEFT JOIN trip_end ed
ON [Link] = [Link] LEFT JOIN customer cu
ON ts.cust_id = [Link]
ORDER BY [Link] ASC
 If you use AI, you must explain what each line of the query is doing.

5. (5 points) Do not use AI. The data only contains one day of trips. Suppose the table
contained data for the entire history of the San Francisco Bike Share. Suppose we
restricted the analysis to only trips that started in March 2014. That is: trip_start.ts >=
'2014-03-01 00:00:00' AND trip_start.ts < '2014-04-01 00:00:00'

Suppose we placed this constraint in the ON clause rather than the WHERE clause.
What unintended consequences would this have on the query result? What does this tell
you about the use of ON and WHERE? Explain your answer in 2-3 sentences.

If we place the filtering condition in “ON” , we would be getting some values which have
nulls for the end trips which started in March and not ended in march but went in April –
edge cases i.e. trips that have no matching end date in march and will lead to
inconsistencies about the trips which actually ended but are showing NULL in the select
query.

If we place the filtering condition of date in “WHERE” clause, we get a proper joined
tables with all the matching rows and then we filter out the records. So , basically
“WHERE” conditions are filtering records post join. However, the ON condition is majorly
on which rows to join and then give the remaining values

6. (5 points) Write a query that finds all customers who have taken at least one trip that
lasted longer than 60 minutes. Your query should return the customer ID from the
customer table, sorted in ascending order. You must use a correlated subquery to
implement a semijoin. Exclude trips that have no end time.

If you use AI, you must explain what each line of the query does.

SELECT [Link] as customer_id

FROM customer cu
WHERE EXISTS (
SELECT [Link]
FROM trip_start ts INNER JOIN trip_end te ON
[Link] = [Link]
WHERE ts.cust_id = [Link]
AND [Link] IS NOT NULL
AND CEIL(TIMESTAMPDIFF(SECOND , [Link] , [Link])/60) > 60
)
ORDER BY [Link];
 7. (4 points) Do not use AI. Write a query that finds all bikes that have been used more
times than the average bike. Return bike_id and trip_count, sorted by trip_count
descending. You must use a scalar subquery in the WHERE or HAVING clause.

SELECT
t1.bike_id ,
COUNT([Link]) as trip_count
FROM trip_start t1 LEFT JOIN trip_end t2
ON [Link] = [Link]
GROUP BY t1.bike_id
HAVING COUNT([Link]) >
( SELECT AVG(trip_count) FROM ( SELECT COUNT([Link]) as trip_count
FROM trip_start t1 LEFT JOIN trip_end t2
ON [Link] = [Link]
GROUP BY t1.bike_id) as subq1 ) ORDER BY trip_count DESC ;

8. (7 points) Do not use AI. Write a query that shows the cumulative number of trips over
time by hour. For each hour, show the trip_hour, hourly_trips (number of trips that
started that hour), and cumulative_trips (total trips from the beginning of the dataset
through that hour). Sort by trip_hour. You will need to find the appropriate function
to extract hour from a timestamp.

SELECT
HOUR(ts) AS trip_hour,
COUNT(id) AS hourly_trips,
SUM(COUNT(id)) OVER (ORDER BY HOUR(ts)) AS cumulative_trips
FROM trip_start
GROUP BY HOUR(ts)
ORDER BY trip_hour;

9. (7 points) Do not use AI. Write a query that calculates a 3-hour moving average of trip
counts. For each hour, show trip_hour, hourly_trips, and moving_avg_trips (the average
hourly trips over the day plus the previous 2 hours, rounded to 2 decimal places). Sort by
trip_hour.

SELECT
HOUR(ts) AS trip_hour,
COUNT(id) AS hourly_trips,
ROUND(AVG(COUNT(id)) OVER ( ORDER BY HOUR(ts) ROWS BETWEEN 2 PRECEDING AND
CURRENT ROW), 2) as moving_avg_trips
FROM trip_start
GROUP BY HOUR(ts)
ORDER BY trip_hour;
 10. (7 points) Do not use AI. Write a query that shows, for each trip, the time elapsed since
the previous trip using the same bike. Return trip_id, bike_id, trip_start, and
minutes_since_last_trip (NULL for the first trip on each bike). Sort by bike_id, then
trip_start.

SELECT [Link] as trip_id ,

ts.bike_id as bike_id ,
[Link] as trip_start ,
TIMESTAMPDIFF(MINUTE, LAG([Link]) OVER (PARTITION BY ts.bike_id ORDER BY
[Link] ) , [Link]) as minutes_since_last_trip
FROM trip_start ts LEFT JOIN trip_end te
ON [Link] = [Link]
ORDER BY bike_id , trip_start;

(10 points) Bonus (Open-ended). You may use AI to write the query.

Currently, users are charged based on the time the bike is in use. Let's instead charge users
based on the distance between the start and end stations, subject to the following conditions:

 Consider only completed rides (rides with an end time)

 Customers pay $3.99 to unlock plus a per-mile charge
 Subscribers pay only a per-mile charge (no unlock fee)
 Use the station table which contains latitude (lat) and longitude (lon)

Your goal: Find the per-mile rates for both Subscribers and Customers that increase SF Bike
Share's total revenue by approximately 5% compared to the time-based pricing from part (4). If
distance-based pricing would decrease revenue, find rates that achieve a 5% increase. If it
would increase revenue by much more than 5%, find rates that cap the increase at
approximately 5% to avoid angering customers.

Approach:

1. Calculate current total revenue using time-based pricing from part (4)
2. Try different combinations of per-mile rates for Subscribers and Customers under
distance-based pricing
3. Find the rates that get closest to a 5% revenue increase

Submit:

 Your query or queries

 The per-mile rates you found (for both Subscribers and Customers)
 Brief explanation (2-3 sentences) of your approach"

Revenue from the time based calculations: 6431.16

Revenue using the same charges but this time by miles : 3521.0668
Inorder to increase the revenue by 5% i.e to make the total revenue = 6431.16*1.05 = 6752.718
the organization has to make an increase of pricing for the Customers as $3.99( as fixed
charge) + $4.5 * miles and for Subscribers: $4 * miles
WITH ride_miles AS (
SELECT
[Link] AS customer_id,
[Link] AS customer_type,
[Link] AS trip_id,
3959 * ACOS(
COS(RADIANS([Link]))
* COS(RADIANS([Link]))
* COS(RADIANS([Link]) - RADIANS([Link]))
+ SIN(RADIANS([Link]))
* SIN(RADIANS([Link]))
) AS miles
FROM trip_start ts
JOIN trip_end ed ON [Link] = [Link]
JOIN station st ON ts.station_id = [Link]
JOIN station st1 ON ed.station_id = [Link]
JOIN customer cu ON ts.cust_id = [Link]
),

charge_cal_customer AS (
SELECT
customer_id,
trip_id,
miles,
CASE
WHEN customer_type = 'Customer'
THEN ROUND(3.99 + 4.5 * miles, 4)
ELSE 0
END AS charge
FROM ride_miles
),

charge_cal_subscriber AS (
SELECT
customer_id,
trip_id,
miles,
CASE
WHEN customer_type = 'Subscriber'
THEN ROUND(4 * miles, 4)
ELSE 0
END AS charge
FROM ride_miles
)

SELECT
(SELECT SUM(charge) FROM charge_cal_customer) AS revenue_from_customers,
(SELECT SUM(charge) FROM charge_cal_subscriber) AS
revenue_from_subscribers;
Explanation for the query:

1) I computed the miles using “Common Table Expression” for only the completed
trips
2) Then I bifurcated the customer segments and used the same dollar value as used
in the time based formula to calculate the revenue , by using the miles as a
multiplier. The total revenue obtained was less than the time based one .
3) As we need 5% approximate increase in revenue i.e.~ $6431.16*1.05 =
$6752.718, I used trial methods computing the revenue for both the segment
which gives me the desired targeted revenue.
4) The trail method gives me the value as : Customers as $3.99( as fixed charge)
+ $4.5 * miles and for Subscribers: $4 * miles