/ -*- coding: utf-8 -*-
/ ---
/ jupyter:
/   jupytext:
/     text_representation:
/       extension: .q
/       format_name: light
/       format_version: '1.5'
/       jupytext_version: 1.14.4
/   kernelspec:
/     display_name: SQL
/     language: sql
/     name: SQL
/ ---

/ + [markdown] azdata_cell_guid="e01663cc-427c-457f-84db-b16d0fca3a90"
/ # Analyze NY taxi data
/ In this tutorial, we will perform exploratory data analysis by combining different Azure Open Datasets using serverless SQL and then visualizing the results in Azure Data Studio. In particular, you analyze the [New York City (NYC) Taxi dataset](https://azure.microsoft.com/services/open-datasets/catalog/nyc-taxi-limousine-commission-yellow-taxi-trip-records/ "https://azure.microsoft.com/services/open-datasets/catalog/nyc-taxi-limousine-commission-yellow-taxi-trip-records/").
/ You can learn more about the meaning of the individual columns in the descriptions of the [NYC Taxi](https://azure.microsoft.com/services/open-datasets/catalog/nyc-taxi-limousine-commission-yellow-taxi-trip-records/ "https://azure.microsoft.com/services/open-datasets/catalog/nyc-taxi-limousine-commission-yellow-taxi-trip-records/"), [Public Holidays](https://azure.microsoft.com/services/open-datasets/catalog/public-holidays/ "https://azure.microsoft.com/services/open-datasets/catalog/public-holidays/"), and [Weather Data](https://azure.microsoft.com/services/open-datasets/catalog/noaa-integrated-surface-data/ "https://azure.microsoft.com/services/open-datasets/catalog/noaa-integrated-surface-data/") Azure open datasets.
/ Let's first get familiar with the NYC Taxi data by running the following query:

/ + azdata_cell_guid="dbc4f12e-388c-49fa-9d85-0fbea3b19d1b"
SELECT TOP 10 * FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [nyc]

/ + [markdown] azdata_cell_guid="a373fa76-bfdf-4bb6-8098-73c9ef436eb8"
/ Similarly, we can explore the Public Holidays dataset:

/ + azdata_cell_guid="48b6ee55-09ec-47df-bea5-707dc2f42aa8"
SELECT TOP 10 * FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/holidaydatacontainer/Processed/*.parquet',
        FORMAT='PARQUET'
    ) AS [holidays]

/ + [markdown] azdata_cell_guid="c4145b77-8663-4e59-914b-721955a02635"
/ Lastly, we can also explore the Weather Data dataset by using the following query:

/ + azdata_cell_guid="f3da158c-c168-45b0-8e38-7ee2d430420f"
SELECT
    TOP 10 *
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/isdweatherdatacontainer/ISDWeather/year=*/month=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [weather]

/ + [markdown] azdata_cell_guid="745b2c81-01eb-4bf5-9cad-47a03dcff194"
/ ## Time series, seasonality, and outlier analysis
/
/ We can easily summarize the yearly number of taxi rides by using the following query:

/ + azdata_cell_guid="e7bacd03-45d4-4b0b-b1d0-9522e1a54436"
SELECT
    YEAR(tpepPickupDateTime) AS current_year,
    COUNT(*) AS rides_per_year
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [nyc]
WHERE nyc.filepath(1) >= '2009' AND nyc.filepath(1) <= '2019'
GROUP BY YEAR(tpepPickupDateTime)
ORDER BY 1 ASC

/ + [markdown] azdata_cell_guid="4397f453-4b20-4083-ae0e-4966d789993f"
/ The data can be visualized in Azure Data Studio by switching from the Table to the Chart view. You can choose among different chart types, such as Area, Bar, Column, Line, Pie, and Scatter.   
/
/ From this visualization, a trend of a decreasing number of rides over years can be clearly seen. Presumably, this decrease is due to the recent increased popularity of ride-sharing companies.
/
/ Next, let's focus the analysis on a single year, for example, 2016. The following query returns the daily number of rides during that year:

/ + azdata_cell_guid="e01007a4-adda-460a-83e0-e45a789c80cb"
SELECT
    CAST([tpepPickupDateTime] AS DATE) AS [current_day],
    COUNT(*) as rides_per_day
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [nyc]
WHERE nyc.filepath(1) = '2016'
GROUP BY CAST([tpepPickupDateTime] AS DATE)
ORDER BY 1 ASC

/ + [markdown] azdata_cell_guid="455f1a05-87bc-4a83-8856-7ac9adc76af0"
/ Again, we can easily visualize data by plotting the Column chart with the Category column set to `current_day` and the Legend (series) column set to `rides_per_day`.
/
/ From the plot chart, you can see there's a weekly pattern, with Saturdays as the peak day. During summer months, there are fewer taxi rides because of vacations. Also, notice some significant drops in the number of taxi rides without a clear pattern of when and why they occur.
/
/ Next, let's see if the drops correlate with public holidays by joining the NYC Taxi rides dataset with the Public Holidays dataset:

/ + azdata_cell_guid="710cb813-a14d-4daa-8436-7a0086e4381f"
WITH
taxi_rides AS
(
    SELECT
        CAST([tpepPickupDateTime] AS DATE) AS [current_day],
        COUNT(*) as rides_per_day
    FROM  
        OPENROWSET(
            BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
            FORMAT='PARQUET'
        ) AS [nyc]
    WHERE nyc.filepath(1) = '2016'
    GROUP BY CAST([tpepPickupDateTime] AS DATE)
),
public_holidays AS
(
    SELECT
        500000 as holiday,
        date
    FROM
        OPENROWSET(
            BULK 'https://azureopendatastorage.blob.core.windows.net/holidaydatacontainer/Processed/*.parquet',
            FORMAT='PARQUET'
        ) AS [holidays]
    WHERE countryorregion = 'United States' AND YEAR(date) = 2016
)
SELECT
*
FROM taxi_rides t
LEFT OUTER JOIN public_holidays p on t.current_day = p.date
ORDER BY current_day ASC

/ + [markdown] azdata_cell_guid="a2160c53-23d2-4243-94ad-7c3408c49080"
/ From the plot chart, you can see that during public holidays the number of taxi rides is lower. There's still one unexplained large drop on January 23. Let's check the weather in NYC on that day by querying the Weather Data dataset:

/ + azdata_cell_guid="aa7087b1-fb8d-43cc-a580-afa1ff6d9741"
SELECT
    AVG(windspeed) AS avg_windspeed,
    MIN(windspeed) AS min_windspeed,
    MAX(windspeed) AS max_windspeed,
    AVG(temperature) AS avg_temperature,
    MIN(temperature) AS min_temperature,
    MAX(temperature) AS max_temperature,
    AVG(sealvlpressure) AS avg_sealvlpressure,
    MIN(sealvlpressure) AS min_sealvlpressure,
    MAX(sealvlpressure) AS max_sealvlpressure,
    AVG(precipdepth) AS avg_precipdepth,
    MIN(precipdepth) AS min_precipdepth,
    MAX(precipdepth) AS max_precipdepth,
    AVG(snowdepth) AS avg_snowdepth,
    MIN(snowdepth) AS min_snowdepth,
    MAX(snowdepth) AS max_snowdepth
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/isdweatherdatacontainer/ISDWeather/year=*/month=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [weather]
WHERE countryorregion = 'US' AND CAST([datetime] AS DATE) = '2016-01-23' AND stationname = 'JOHN F KENNEDY INTERNATIONAL AIRPORT'

/ + [markdown] azdata_cell_guid="be7f6d54-0301-4fbd-be89-56472649e6f9"
/ The results of the query indicate that the drop in the number of taxi rides occurred because:
/
/ -   There was a blizzard on that day in NYC with heavy snow (~30 cm).
/ -   It was cold (temperature was below zero degrees Celsius).
/ -   It was windy (~10 m/s).
/
/ This tutorial has shown how a data analyst can quickly perform exploratory data analysis, easily combine different datasets by using serverless Synapse SQL pool, and visualize the results by using Azure Data Studio.

StarcoderdataJupyterScriptsDedupFiltered