Scribd
Upload
7 views2 pages

Sub Query

The first SQL query retrieves the number of orders fulfilled by each warehouse and categorizes their fulfillment rates into three groups based on the percentage of total orders. The second SQL query calculates the trip duration and the average trip duration for each station in the New York CitiBike dataset, providing the difference from the average for each trip. Both queries utilize subqueries to enhance their calculations and provide detailed insights into warehouse performance and bike trip statistics.

Uploaded by

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

Sub Query

The first SQL query retrieves the number of orders fulfilled by each warehouse and categorizes their fulfillment rates into three groups based on the percentage of total orders. The second SQL query calculates the trip duration and the average trip duration for each station in the New York CitiBike dataset, providing the difference from the average for each trip. Both queries utilize subqueries to enhance their calculations and provide detailed insights into warehouse performance and bike trip statistics.

Uploaded by

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

SELECT

Warehouse.warehouse_id,
CONCAT(Warehouse.state, ': ', Warehouse.warehouse_alias) AS warehouse_n
ame,
COUNT(Orders.order_id) AS number_of_orders,
(SELECT COUNT(*) FROM your-project.warehouse_orders.orders AS Orders) A
S total_orders,
CASE
WHEN COUNT(Orders.order_id)/(SELECT COUNT(*) FROM your-
project.warehouse_orders.orders AS Orders) <= 0.20
THEN 'Fulfilled 0-20% of Orders'
WHEN COUNT(Orders.order_id)/(SELECT COUNT(*) FROM your-
project.warehouse_orders.orders AS Orders) > 0.20
AND COUNT(Orders.order_id)/(SELECT COUNT(*) FROM your-
project.warehouse_orders.orders AS Orders) <= 0.60
THEN 'Fulfilled 21-60% of Orders'
ELSE 'Fulfilled more than 60% of Orders'
END AS fulfillment_summary
FROM your-project.warehouse_orders.warehouse AS Warehouse
LEFT JOIN your-project.warehouse_orders.orders AS Orders
ON Orders.warehouse_id = Warehouse.warehouse_id
GROUP BY
Warehouse.warehouse_id,
warehouse_name
HAVING
COUNT(Orders.order_id) > 0
SELECT
starttime,
start_station_id,
tripduration,
(
SELECT ROUND(AVG(tripduration),2)
FROM bigquery-public-data.new_york_citibike.citibike_trips
WHERE start_station_id = outer_trips.start_station_id
) AS avg_duration_for_station,
ROUND(tripduration - (
SELECT AVG(tripduration)
FROM bigquery-public-data.new_york_citibike.citibike_trips
WHERE start_station_id = outer_trips.start_station_id), 2) AS dif
ference_from_avg
FROM bigquery-public-data.new_york_citibike.citibike_trips AS outer_trips
ORDER BY difference_from_avg DESC
LIMIT 25;

You might also like