SQL Case Study

Format for Germany

SELECT

    -- Cast PickupDate as a date and display as a German date

FORMAT(CAST(PickupDate AS date), 'd', 'de-de') AS 'PickupDate',

Zone.Borough,

    -- Display TotalDistance in the German format

FORMAT(SUM(TripDistance), 'n', 'de-de') AS 'TotalDistance',

    -- Display TotalRideTime in the German format

FORMAT(SUM(DATEDIFF(minute, PickupDate, DropoffDate)), 'n', 'de-de') AS 'TotalRideTime',

    -- Display TotalFare in German currency

TotalRideTime(SUM(TotalAmount), 'c', 'de-de') AS 'TotalFare'

FROM YellowTripData

INNER JOIN TaxiZoneLookup AS Zone 

ON PULocationID = Zone.LocationID 

GROUP BY

CAST(PickupDate as date),

    Zone.Borough 

ORDER BY

CAST(PickupDate as date),

    Zone.Borough;



NYC Borough statistics SP

It's time to apply what that you have learned in this course and write a stored procedure to solve the first objective of the Taxi Ride business case. Calculate AvgFarePerKM, RideCount and TotalRideMin for each NYC borough and weekday. After discussion with stakeholders, you should omit records where the TripDistance is zero

CREATE OR ALTER PROCEDURE dbo.cuspBoroughRideStats

AS

BEGIN

SELECT

    -- Calculate the pickup weekday

DATENAME(weekday, PickupDate) AS 'Weekday',

    -- Select the Borough

Zone.Borough AS 'PickupBorough',

    -- Display AvgFarePerKM as German currency

FORMAT(AVG(dbo.ConvertDollar(TotalAmount, .88)/dbo.ConvertMiletoKM(TripDistance)), 'c', 'de-de') AS 'AvgFarePerKM',

    -- Display RideCount in the German format

FORMAT(COUNT(ID), 'n', 'de-de') AS 'RideCount',

    -- Display TotalRideMin in the German format

FORMAT(SUM(DATEDIFF(SECOND, PickupDate, DropOffDate))/60, 'n', 'de-de') AS 'TotalRideMin'

FROM YellowTripData

INNER JOIN TaxiZoneLookup AS Zone 

ON PULocationID = Zone.LocationID

-- Only include records where TripDistance is greater than 0

WHERE TripDistance > 0

-- Group by pickup weekday and Borough

GROUP BY DATENAME(WEEKDAY, PickupDate), Zone.Borough

ORDER BY CASE WHEN DATENAME(WEEKDAY, PickupDate) = 'Monday' THEN 1

        WHEN DATENAME(WEEKDAY, PickupDate) = 'Tuesday' THEN 2

              WHEN DATENAME(WEEKDAY, PickupDate) = 'Wednesday' THEN 3

              WHEN DATENAME(WEEKDAY, PickupDate) = 'Thursday' THEN 4

              WHEN DATENAME(WEEKDAY, PickupDate) = 'Friday' THEN 5

              WHEN DATENAME(WEEKDAY, PickupDate) = 'Saturday' THEN 6

              WHEN DATENAME(WEEKDAY, PickupDate) = 'Sunday' THEN 7 END,  

SUM(DATEDIFF(SECOND, PickupDate, DropOffDate))/60

DESC

END;

Execution of the Above Procedure

-- Create SPResults

DECLARE @SPResults TABLE(

  -- Create Weekday

Weekday nvarchar(30),

    -- Create Borough

Borough nvarchar(30),

    -- Create AvgFarePerKM

AvgFarePerKM nvarchar(30),

    -- Create RideCount

RideCount nvarchar(30),

    -- Create TotalRideMin

TotalRideMin nvarchar(30))


-- Insert the results into @SPResults

INSERT INTO @SPResults

-- Execute the SP

EXEC dbo.cuspBoroughRideStats


-- Select all the records from @SPresults 

SELECT *

FROM @SPresults;

Comments

Post a Comment

Popular posts from this blog

Binomial Test in Python

  Binomial Test Let’s imagine that we are analyzing the percentage of customers who make a purchase after visiting a website. 1000 customers visited the site this month, and 58 of them made a purchase. The marketing department reports that historical data suggests about 72 of every 1000 visitors make a purchase. Thus, they estimate that the probability of any particular customer making a purchase is 7.2%. We would like to know if this month’s number, 58 purchases, is significantly different from normal or a reasonable fluctuation due to random chance. In previous exercises, we collected samples of numerical information (eg. order price) and then used the mean and standard deviation of those samples to make comparisons. In contrast, we now have a sample where each unit (a visitor) falls into one of two discrete categories: “made a purchase” “did not make a purchase” Instead of comparing sample means, we want to compare the percent in the “made a purchase” category to some expectatio...
Read more

Two - Sample T test

  2 Sample T-Test Suppose that a company has recently updated their website to make it more colorful and inviting. The company wants to know whether the new design is resulting in visitors staying on the site for a longer period of time. A sample of 100 visitors who saw the old design spent an average of 25 minutes on the site. A second sample of 100 visitors who saw the new version spent an average of 28 minutes on the site. Did the average time spent per visitor vary across groups? Or is this difference attributable to random chance? One way of testing whether this difference is significant is by using a  2 Sample T-Test . A 2 Sample T-Test compares two sets of numerical data. The null hypothesis of a 2 Sample T-Test is that the two observed samples come from populations with the same mean. In the example above, this means: if we could observe all site visitors in two alternate universes (one where they see each version of the site), the average visiting times in these unive...
Read more

Web Scraping using JSON and API

JSON–from the web to Python Wow, congrats! You've just queried your first API programmatically in Python and printed the text of the response to the shell. However, as you know, your response is actually a JSON, so you can do one step better and decode the JSON. You can then print the key-value pairs of the resulting dictionary. That's what you're going to do now! Pass the variable  url  to the  requests.get()  function in order to send the relevant request and catch the response, assigning the resultant response message to the variable  r . Apply the  json()  method to the response object  r  and store the resulting dictionary in the variable  json_data . # Import package import   requests # Assign URL to variable: url url  =  'http://www.omdbapi.com/?apikey=72bc447a&t=social+network' # Package the request, send the request and catch the...
Read more