Useful SQL Query Collection

[schnuerle]

Post as comments here sample SQL queries that you find useful. Make sure to:

• say what you use it for, eg a title
• show how people can modify it for themselves
• explain its value or how it integrates into a use case
• how long it takes to run and note any suggestions for database indexes that might speed up the query
• wrap the SQL with code markdown formatting, eg using ``` around the SQL

DB Testing Example

Not that useful but shows off some joins and runs quickly.

A quick query to select weather hazard alerts joined with road type and returns the latitude and longitude of the location. Take a few seconds to run if you have some new recommended indexes setup.

select a.pub_utc_date, a.type, a.subtype, a.street, a.reliability, 
 r."name", c.latitude, c.longitude 
from waze.alerts a 
join waze.roads r on a.road_type = r.value
join waze.coordinates c on a.id = c.alert_id
where a.type = 'WEATHERHAZARD'
limit 100;

[schnuerle]

Here's the query I ran to extract the 2018 data to get a few hours of jams city-wide to do a post-event analysis in Carto so we can improve traffic flow the next year.

> select j.pub_utc_date::TIMESTAMPTZ AT TIME ZONE 'US/Eastern' as EasternTime, 
> j.level, j.delay, j.road_type, j.street, 
> c.latitude, c.longitude,
> j.pub_utc_date as UTCtime
> from waze.jams j
> join waze.coordinates c on c.jam_id = j.id 
> where j.pub_utc_date >= '2018-04-21 21:00:00' and j.pub_utc_date <= '2018-04-22 03:30:00'
> order by utctime asc;

Data extracted:
https://louisvillemetro-ms.carto.com/dataset/thunder_jams_4_22_18_5p_1130p

Animated map of results:
https://louisvillemetro-ms.carto.com/builder/85bb5262-a0be-492c-b04a-bda991b7d0c1/layers

Non animated version of 2017 data:
https://louisvillemetro-ms.carto.com/builder/17c31086-4584-4e49-972a-eb3732e25ae5

[mjhampton12182]

Below is a query for re-timing analysis to use in Power BI. You can find the Power BI template in my comments here #28. The date/time field is set to Eastern Standard Time, but can be changed by updating 'EST' with whatever time zone you need. You can update the time group field based on what your traffic team says if peak hours. At the bottom in the where statement you can change the to and from dates you want to pull data. You can also change the range for latitude and longitude. The amount of time this takes to run varies on what changes you make in the where statement. It also depends on your computer and network connection speeds.

SELECT
j.id
,j.uuid
,j.pub_millis
,( ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') AS EST_DATE_TIME
,date_trunc('MONTH', ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') AS MONTH_YEAR
,case
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') = '7' then 'Sunday'
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') = '1' then 'Monday'
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') = '2' then 'Tuesday'
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') = '3' then 'Wednesday'
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') = '4' then 'Thursday'
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') = '5' then 'Friday'
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') = '6' then 'Saturday'
else 'N/A'
end as DAY_OF_WEEK
,case
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') between 1 /*Monday*/ and 5 /*Friday*/ and EXTRACT(HOUR FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') <= 6 /*12:00am-6:59am*/ then 'Weekday Night'
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') between 1 /*Monday*/ and 5 /*Friday*/ and EXTRACT(HOUR FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') between 7 and 8 /*7:00am-8:59am*/ then 'Weekday AM Peak'
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') between 1 /*Monday*/ and 5 /*Friday*/ and EXTRACT(HOUR FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') between 9 and 10 /*9:00am-10:59am*/ then 'Weekday Late morning off-peak'
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') between 1 /*Monday*/ and 5 /*Friday*/ and EXTRACT(HOUR FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') between 11 and 12 /*11:00am-12:59pm*/ then 'Weekday Mid-day'
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') between 1 /*Monday*/ and 5 /*Friday*/ and EXTRACT(HOUR FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') between 13 and 15 /*1:00pm-3:59pm*/ then 'Weekday Mid-afternoon'
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') between 1 /*Monday*/ and 5 /*Friday*/ and EXTRACT(HOUR FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') between 16 and 17 /*4:00pm-5:59pm*/ then 'Weekday PM peak'
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') between 1 /*Monday*/ and 5 /*Friday*/ and EXTRACT(HOUR FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') between 18 and 20 /*6:00pm-8:59pm*/ then 'Weekday Evening'
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') between 1 /*Monday*/ and 5 /*Friday*/ and EXTRACT(HOUR FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') >= 21 /*9:00pm-11:59pm*/ then 'Weekday Night'
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') = 6 /*Saturday*/ and EXTRACT(HOUR FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') <= 9 /*12:00am-9:59am*/ then 'Saturday Night'
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') = 6 /*Saturday*/ and EXTRACT(HOUR FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') between 10 and 15 /*10:00am-3:59pm*/ then 'Saturday Day'
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') = 6 /*Saturday*/ and EXTRACT(HOUR FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') between 16 and 19 /*4:00pm-7:59pm*/ then 'Saturday Evening'
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') = 6 /*Saturday*/ and EXTRACT(HOUR FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') >= 20 /*8:00pm-11:59pm*/ then 'Saturday Night'
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') = 7 /*Sunday*/ and EXTRACT(HOUR FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') <= 9 /*12:00am-9:59am*/ then 'Sunday Night'
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') = 7 /*Sunday*/ and EXTRACT(HOUR FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') between 10 and 17 /*10:00am-5:59pm*/ then 'Sunday Day'
when EXTRACT(ISODOW FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') = 7 /*Sunday*/ and EXTRACT(HOUR FROM ((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST') >= 18 /*6:00pm-11:59pm*/ then 'Sunday Night'
else 'N/A'
end as TIME_GROUP
,j.street
,j.start_node
,j.end_node
,j.road_type
,j.blocking_alert_id
,j.delay
,j.speed
,j.length
,j.type
,j.level
,c.latitude
,c.longitude
,c.order
,j.city
,j.country

FROM waze_data.waze.jams j
left join waze_data.waze.coordinates c on j.id=c.Jam_id

where 
(
((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST' between '2016-12-01' and '2017-02-28' /*dates before retiming efforts*/
or
((j.pub_utc_date) AT TIME ZONE 'UTC') AT TIME ZONE 'EST' between '2017-12-01' and '2018-02-28' /*dates for comparison after retiming efforts*/  
)
and
c.longitude /*longitude*/ between -85.58179 /*left longitude value for the area*/ and -85.53871 /*right longitude value for the area*/
and
c.latitude /*latitude*/ between 38.28423 /*lower latitude value for the area*/ and 38.30009 /*upper latitude value for the area*/