-- Percentages for all month in 2017
select
   month(date) as `Month`
  ,count(1) / ( select count(1) from crimes_2017 where PrimaryType = 'HOMICIDE') * 100 as `% of murders`
from crimes_2017
where PrimaryType = 'HOMICIDE'
group by `Month`
order by `Month`

-- Which crimes happened in 2017 but not in 2018?
select distinct PrimaryType
from crimes_2017
where PrimaryType NOT IN ( select distinct PrimaryType from crimes_2018 )

%sql
-- We can use the column name from the subquery, which makes it easier to read
select date_format(`First crime date`, 'dd.MM.y hh:mm') || ' Uhr' as `First crime date`
      ,date_format(`Last crime date`, 'dd.MM.y hh:mm') || ' Uhr' as `Last crime date`
      ,`Time between` || ' days' as `Time between`
from myView

%sql
-- Create a view with only the columns we really need
create or replace view crimes_2018_clean as
select id
      ,Date 
      ,Block
      ,IUCR
      ,PrimaryType
      ,Description
      ,LocationDescription
      ,Arrest
from crimes_2018

%sql
-- Create a view that contains the data for both years
create or replace view crimes_all as
  select * from crimes_2018
  UNION ALL
  select * from crimes_2017

select dayofyear(date) as `Day of year`
      ,date_format(date, 'HH:mm') as `Time of day`
      ,PrimaryType
      ,date
      ,count(1) over (partition by dayofyear(date)) as `Total crimes for that day`
from crimes_2017
-- Examlpe for day 10
where dayofyear(date) = 10
order by `Total crimes for that day` desc

-- Get all homicides along with the time of the subsequent homicide
select date as `Date of current homicide`
      ,primaryType as `Type of current crime`
      ,lead(date) over (order by date) as `Date of next homicide`
      ,lead(PrimaryType) over (order by date) as `Type of next crime`
from crimes_2018
where PrimaryType = 'HOMICIDE'