Scribd
Upload
9 views3 pages

SQL Queries for Trip Fare Analysis

The document outlines SQL queries for data manipulation and analysis, including examples of using window functions to calculate totals and differences in trip fares. It also describes the structure of a 'trips' table and provides a method to calculate the weekly percentage change in total trip fare for each city. Several Common Table Expressions (CTEs) are used to organize and compute the required data efficiently.

Uploaded by

Shashwat Dev
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
9 views3 pages

SQL Queries for Trip Fare Analysis

The document outlines SQL queries for data manipulation and analysis, including examples of using window functions to calculate totals and differences in trip fares. It also describes the structure of a 'trips' table and provides a method to calculate the weekly percentage change in total trip fare for each city. Several Common Table Expressions (CTEs) are used to organize and compute the required data efficiently.

Uploaded by

Shashwat Dev
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

From

Join
on
where
group by
having
select
order by
limit

A B
1 1
1 1
1 2
1 2
2 3
2 4
null null
null

A B
1 1

Id dept amount

1 A 100
2 A 200
5 C 300
4 C 400
3 B 500

Select *, sum(amount) over() as total from table


Select *, sum(amount) over(order by Id) as total from table
Select *, sum(amount) over(partition by dept order by Id) as total from table

1 A 100 100
2 A 200 300
5 C 300 700
4 C 400 400
3 B 500 500

Table trips: trip_id, rider_id, city_name, trip_timestamp, trip_fare_usd, state The


state might be one of the following: SETTLED, ERROR, CHARGEBACK Create a query that
show weekly percentage of increase or decrease in terms of total trip fare for each
city.

with cte as
(
select *, trip_timestamp::date, extract(month from trip_timestamp) as month,
extract(year from trip_timestamp) as years
from trips
),
cte2 as
(
select city_name, month, year, sum(trip_fare_usd) as total_fare
from cte
group by city_name, month, year
),
cte3 as
(
select city_name, month, year, total_fare, lag(total_fare,1,total_fare)
over(partition by city_name order by year, month) as prev_fare
from cte2
),
cte4 as
(
select city_name, month, year, (total_fare- prev_fare) *100/prev_fare as diff
from cte3
)
select * from cte4;
/*
cte5 as
(
select city_name, sum(total_fare) as city_total
from cte2
group by city_name
)
select a.city_name,[Link], [Link], [Link]*100/a.city_total as monthly_change
from cte5 as a
left join cte4 as b
on a.city_name=b.city_name;*/

You might also like