SQL Functions Advance

1] Use CONVERT() to SELECT and GROUP BY the date portion of the StartDate  

SELECT

  -- Select the date portion of StartDate

  CONVERT(DATE, StartDate) as StartDate,

  -- Measure how many records exist for each StartDate

  COUNT(StartDate) as CountOfRows 

FROM CapitalBikeShare 

-- Group by the date portion of StartDate

GROUP BY CONVERT(DATE, StartDate)

-- Sort the results by the date portion of StartDate

ORDER BY CONVERT(DATE, StartDate)




2]

  • Use DATENAME() to SELECT the weekday value for the StartDate.
  • Use SUM() and DATEDIFF() to calculate TotalTripHours. (beginning with seconds).
  • Group by the DATENAME() result and summarize TotalTripHours.
  • Order TotalTripHours in descending order.

Ans - 

SELECT
    -- Select the day of week value for StartDate
DATENAME(Weekday, StartDate) as DayOfWeek,
    -- Calculate TotalTripHours
SUM(DATEDIFF(ss, StartDate, EndDate))/ 3600 as TotalTripHours 
FROM CapitalBikeShare 
-- Group by the day of week
GROUP BY DATENAME(WEEKDAY,StartDate)
-- Order TotalTripHours in descending order
ORDER BY TotalTripHours DESC;


3 
  • Use SUM() and DATEDIFF() to find the Total Ride Hours per day starting from seconds.
  • Use CONVERT() to SELECT the date portion of StartDate.
  • Use DATENAME() and CONVERT() to select the WEEKDAY.
  • Use WHERE to only include Saturdays


Answer 

SELECT
-- Calculate TotalRideHours using SUM() and DATEDIFF()
  SUM(DATEDIFF(SECOND, StartDate, EndDate))/ 3600 AS TotalRideHours,
    -- Select the DATE portion of StartDate
  CONVERT(DATE, StartDate) AS DateOnly,
    -- Select the WEEKDAY
  DATENAME(WEEKDAY, CONVERT(DATE, StartDate)) AS DayOfWeek 
FROM CapitalBikeShare
-- Only include Saturday
WHERE DATENAME(WEEKDAY, StartDate) = 'Saturday' 
GROUP BY CONVERT(DATE, StartDate);




DECLARE VARIABLE Function Syntax


  • Create a time variable named @ShiftStartTime and set initial value to '08:00 AM'.
  • Create a date variable named @StartDate and set it to the first StartDate from the BikeShare table.
  • Create a datetime variable named `@ShiftStartDateTime.
  • Change @StartDate and @ShiftStartTime to datetime data types and assign to @ShiftStartDateTime


Answer - 


-- Create @ShiftStartTime
DECLARE @ShiftStartTime AS time = '08:00 AM'

-- Create @StartDate
DECLARE @StartDate AS date

-- Set StartDate to the first StartDate from CapitalBikeShare
SET 
@StartDate = (
    SELECT TOP 1 StartDate 
    FROM CapitalBikeShare 
    ORDER BY StartDate ASC
)

-- Create ShiftStartDateTime
DECLARE @ShiftStartDateTime AS datetime

-- Cast StartDate and ShiftStartTime to datetime data types
SET @ShiftStartDateTime = CAST(@StartDate AS datetime) + CAST(@ShiftStartTime AS datetime) 

SELECT @ShiftStartDateTime



Declare Syntax

  • Use DECLARE to create a TABLE variable named @Shifts
  • The @Shifts table variable should have the following columns - StartDateTime and EndDateTime - both of datetime data type.
  • Populate the table variable with the values '3/1/2018 8:00 AM' and '3/1/2018 4:00 PM'.


-- Declare @Shifts as a TABLE
DECLARE @Shifts TABLE(
    -- Create StartDateTime column
StartDateTime datetime ,
    -- Create EndDateTime column
EndDateTime datetime)
-- Populate @Shifts
INSERT INTO @shifts (StartDateTime, EndDateTime)
SELECT '3/1/2018 8:00 AM', '3/1/2018 4:00 PM'
SELECT * 
FROM @Shifts




4. 
  • Declare a TABLE variable named @RideDates with the following columns RideStart and RideEnd.
  • Both table variable columns should be date data type.
  • SELECT the unique values of StartDate and EndDate from the CapitalBikeShare table. CAST them from datetime to date data types.
  • Store the query results in @RideDates

-- Declare @RideDates
DECLARE @RideDates TABLE(
    -- Define RideStart column
RideStart date, 
    -- Define RideEnd column
    RideEnd date)
-- Populate @RideDates
INSERT INTO @RideDates(RideStart, RideEnd)
-- Select the unique date values of StartDate and EndDate
SELECT DISTINCT
    -- Cast StartDate as date
CAST(StartDate as date),
    -- Cast EndDate as date
CAST(EndDate as date) 
FROM CapitalBikeShare 
SELECT * 
FROM @RideDates



Here you will use the GETDATE(), DATEDIFF(), and DATEADD() functions to find the first day of the current month.


SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

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

Slicing and Indexing in Python Pandas

 # Make a list of cities to subset on cities = ["Moscow", "Saint Petersburg"] # Subset temperatures using square brackets print(temperatures[temperatures["city"].isin(cities)]) # Subset temperatures_ind using .loc[] print(temperatures_ind.loc[cities]) temperatures_ind = temperatures.set_index(['country','city']) # List of tuples: Brazil, Rio De Janeiro & Pakistan, Lahore rows_to_keep = [('Brazil','Rio De Janeiro'),('Pakistan','Lahore')] # Subset for rows to keep print(temperatures_ind.loc[rows_to_keep]) # Sort temperatures_ind by index values print(temperatures_ind.sort_index()) # Sort temperatures_ind by index values at the city level print(temperatures_ind.sort_index(level="city")) # Sort temperatures_ind by country then descending city print(temperatures_ind.sort_index(level=["country", "city"], ascending = [True, False])) Slice rows with code like  df.loc[("a", ...
Read more

Python Syntax and Functions Part2 (Summary Statistics)

The  .agg()  method allows you to apply your own custom functions to a DataFrame, as well as apply functions to more than one column of a DataFrame at once, making your aggregations super efficient. In the custom function for this exercise, "IQR" is short for inter-quartile range, which is the 75th percentile minus the 25th percentile. It's an alternative to standard deviation that is helpful if your data contains   import numpy as np def iqr(column):     return column.quantile(0.75) - column.quantile(0.25) # Update to print IQR and median of temperature_c, fuel_price_usd_per_l, & unemployment print(sales[["temperature_c", "fuel_price_usd_per_l", "unemployment"]].agg([iqr, np.median])) Calulating Cumulative Statistics Syntax = df.drop_duplicates sales_1_1 = sales_1_1.sort_values("date") # Get the cumulative sum of weekly_sales, add as cum_weekly_sales col sales_1_1["cum_weekly_sales"] = sales_1_1["weekly_sales...
Read more