GoSpotCheck Coding Exercise

aggregate_view_categories.sql

-- Step 6
create view category_aggregate (category, total_places, total_chairs) as
	select
		category,
		count(cafe),
		sum(number_of_chairs)
	from street_cafes group by category;

category_update.rb

#!/usr/bin/ruby
# Placed into a module for testing
# Step 5 - methods to update the category column
module Categories
  def find_category(sql_row)
    chairs = sql_row['number_of_chairs'].to_i
    if sql_row['post_code'].start_with?("LS1 ")
      case(chairs)
      when 0..10
        category="LS1 Small"
      when 11..100
        category="LS1 Medium"
      when 101..1000
        category="LS1 Large"
      else
        category="Not a supported number"
      end
    elsif sql_row['post_code'].start_with?("LS2 ")
      case(chairs)
      when 0..10
        category="LS2 Small"
      when 11..100
        category="LS2 Medium"
      when 101..1000
        category="LS2 Large"
      else
        category="Not a supported number"
      end
    else
      category="other"
    end
    category
  end

  def category_update(con)
    begin
      # Run our query
      rows = con.sync_exec "SELECT * FROM street_cafes"
      # Check the results
      rows.each do |row|
        category = find_category(row)
        con.exec "UPDATE street_cafes SET category='#{category}' WHERE id=#{row['id']}"
        puts "%s %s" % [ row['cafe'], row['post_code']]
      end
    rescue PG::Error => e
      puts e.message
    ensure
      rows.clear if rows
    end
  end
end

category_update_spec.rb

require 'rspec'
require_relative 'category_update'

describe Categories do
  include Categories
  it "should sort the cafe into LS1 Small category" do
    @row = Hash['cafe' => "Test Cafe", 'post_code' => "LS1 202", 'number_of_chairs' => 10]
    expect(find_category(@row)).to eq("LS1 Small")
  end
  it "should sort the cafe into LS1 Medium category" do
    @row = Hash['cafe' => "Test Cafe", 'post_code' => "LS1 202", 'number_of_chairs' => 15]
    expect(find_category(@row)).to eq("LS1 Medium")
  end
  it "should sort the cafe into LS1 Large category" do
    @row = Hash['cafe' => "Test Cafe", 'post_code' => "LS1 202", 'number_of_chairs' => 150]
    expect(find_category(@row)).to eq("LS1 Large")
  end
  it "should sort the cafe into LS2 Small category" do
    @row = Hash['cafe' => "Test Cafe", 'post_code' => "LS2 202", 'number_of_chairs' => 10]
    expect(find_category(@row)).to eq("LS2 Small")
  end
  it "should sort the cafe into LS2 Medium category" do
    @row = Hash['cafe' => "Test Cafe", 'post_code' => "LS2 202", 'number_of_chairs' => 15]
    expect(find_category(@row)).to eq("LS2 Medium")
  end
  it "should sort the cafe into LS2 Large category" do
    @row = Hash['cafe' => "Test Cafe", 'post_code' => "LS2 202", 'number_of_chairs' => 150]
    expect(find_category(@row)).to eq("LS2 Large")
  end
  it "should sort the cafe into the 'other' category" do
    @row = Hash['cafe' => "Test Cafe", 'post_code' => "LS10 202", 'number_of_chairs' => 150]
    expect(find_category(@row)).to eq("other")
  end
end

concat_and_write.rb

#!/usr/bin/ruby
# Placed into a module for testing
# Step 7b
module Concat_And_Write
  def concat_and_write(sql_row)
    cafe_name = "#{sql_row['category']} #{sql_row['cafe']}"
    cafe_name.sub!("'", "\\\'")
    sql_statement = "UPDATE public.street_cafes SET cafe='#{cafe_name}' WHERE id=#{sql_row['id']};"
  end
end

concat_and_write_spec.rb

require 'rspec'
require_relative 'concat_and_write'

describe Concat_And_Write do
  include Concat_And_Write
  it "should return an sql statement with the concatenated name" do
    @row = Hash['id' => 99, 'cafe' => "Test Cafe", 'post_code' => "LS1 202", 'number_of_chairs' => 100, 'category' => "LS1 Large"]
    expect(concat_and_write(@row)).to eq("UPDATE public.street_cafes SET cafe='LS1 Large Test Cafe' WHERE id=99;")
  end
end

create_db.sql

-- Prerequisite for running the gsc_db_script.rb
DROP DATABASE [IF EXISTS] gsc;

CREATE DATABASE gsc
    WITH
    OWNER = #{ENV['GSC_DATABASE_USERNAME']}
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

create_table_street_cafes.sql

-- Step 3
DROP TABLE IF EXISTS street_cafes CASCADE;
CREATE TABLE street_cafes
(
    id serial primary key,
    cafe character varying(100) NOT NULL,
    street_address character varying(120) NOT NULL,
    post_code character varying(10) NOT NULL,
    number_of_chairs integer NOT NULL,
    notes text,
    category text
)
WITH (
    OIDS = FALSE
);

export_and_drop.rb

require 'csv'
# Placed into a module for testing
# Step 7a
module Export_And_Drop
  def write_to_file(filename, row_data)
    CSV.open(filename, "ab") do |csv|
      csv << row_data
    end
  end

  def export_and_drop(sql_row)
    write_to_file('export.csv', [sql_row["cafe"],sql_row["street_address"],sql_row["post_code"],sql_row["number_of_chairs"],sql_row["category"]])
    sql_statment = "DELETE FROM street_cafes WHERE id=#{sql_row['id']};"
  end
end

export_and_drop_spec.rb

require 'rspec'
require_relative 'export_and_drop'

describe Export_And_Drop do
  include Export_And_Drop
  it "should return an SQL statement to delete the row provided" do
    @row = Hash['id' => 99, 'cafe' => "Test Cafe", 'post_code' => "LS1 202", 'number_of_chairs' => 100, 'category' => "LS1 Large"]
    expect(export_and_drop(@row)).to eq("DELETE FROM street_cafes WHERE id=99;")
  end
end

export_output.csv

gsc_db_script.rb

#!/usr/bin/ruby
require 'pg'
require 'csv'

########## Coding Exercise for GoSpotCheck by Schuyler Ankele ###########
postgres_username = ENV['GSC_DATABASE_USERNAME']
postgres_password = ENV['GSC_DATABASE_PASSWORD']

# Connect to Postgres (After create_db.sql is executed and db 'gsc' is available)
postgres = PG.connect :dbname => 'gsc', :user => postgres_username, :password => postgres_password

CREATE_TABLE = "
DROP TABLE IF EXISTS street_cafes CASCADE;
CREATE TABLE street_cafes
(
    id serial primary key,
    cafe character varying(100) NOT NULL,
    street_address character varying(120) NOT NULL,
    post_code character varying(10) NOT NULL,
    number_of_chairs integer NOT NULL,
    notes text,
    category text
)
WITH (
    OIDS = FALSE
);"

# Seed the DB - Loads data from street_cafe_seed.csv | Source https://datahub.io/dataset/street-cafes-licences-in-leeds
SEED_BASH_COMMAND = "psql -d gsc --command  \"\\copy public.street_cafes (cafe, street_address, post_code, number_of_chairs, notes, category) FROM '/Users/sankele/gsc_scripts/street_cafes_seed.csv' DELIMITER '|' CSV HEADER QUOTE '\\\"' ESCAPE '''';\""

# Create Views Step 4
POST_CODE_DETAILS_SQL = "
  create view post_code_details (post_code, total_places, total_chairs, chairs_pct) as
    select
    post_code,
    count(cafe),
        sum(number_of_chairs),
        (cast(sum(number_of_chairs) as decimal(5))/(select cast(sum(number_of_chairs)as decimal(5)) from street_cafes))
    from street_cafes group by post_code;"

POST_CODE_SUMMARY_SQL = "
  create view post_code_summary (place_with_max_chairs, max_chairs) as
    select cafe, number_of_chairs from street_cafes where number_of_chairs = (select max(number_of_chairs) from street_cafes);"

# Problem 5 - method to update the category column
def find_category(sql_row)
  chairs = sql_row['number_of_chairs'].to_i
  if sql_row['post_code'].start_with?("LS1 ")
    case(chairs)
    when 0..10
      category="LS1 Small"
    when 11..100
      category="LS1 Medium"
    else
      category="LS1 Large"
    end
  elsif sql_row['post_code'].start_with?("LS2 ")
    case(chairs)
    when 0..10
      category="LS2 Small"
    when 11..100
      category="LS2 Medium"
    else
      category="LS2 Large"
    end
  else
    category="other"
  end
  category
end

def category_update(con)
  begin
    # Run a query for all rows
    rows = con.sync_exec "SELECT * FROM street_cafes"
    # Parse the results to find the categories
    rows.each do |row|
      category = find_category(row)
      con.exec "UPDATE street_cafes SET category='#{category}' WHERE id=#{row['id']}"
      puts "%s %s" % [ row['cafe'], row['post_code']]
    end
  rescue PG::Error => e
    puts e.message
  ensure
    rows.clear if rows
  end
end

# Problem 6
AGGREGATE_VIEW_SQL = "
  create view category_aggregate (category, total_places, total_chairs) as
	select
		category,
		count(cafe),
		sum(number_of_chairs)
	from street_cafes group by category;"

# Problem 7a
# Write to a CSV
def write_to_file(filename, row_data)
  CSV.open(filename, "ab") do |csv|
    csv << row_data
  end
end

# Drop the row if the category is 'Small' and has been exported
def export_and_drop(sql_row)
  write_to_file('export.csv', [sql_row["cafe"],sql_row["street_address"],sql_row["post_code"],sql_row["number_of_chairs"],sql_row["category"]])
  sql_statement = "DELETE FROM public.street_cafes WHERE id=#{sql_row['id']};"
end

# Problem 7b
# Update if the category is 'Medium' or 'Large'
def concat_and_write(sql_row)
  cafe_name = "#{sql_row['category']} #{sql_row['cafe']}"
  cafe_name.sub!("'", "\\\'")
  sql_statement = "UPDATE public.street_cafes SET cafe='#{cafe_name}' WHERE id=#{sql_row['id']};"
end

# Problem 7 Implementation
def export_or_update(con)
  begin
    # Run our query
    rows = con.sync_exec "SELECT * FROM street_cafes"
    # Check the results
    rows.each do |row|
      category = row["category"]
      case
      when category.include?("Small")
        sql_statement = export_and_drop(row)
        con.sync_exec sql_statement
      when category.include?("Medium") || category.include?("Large")
        sql_statement = concat_and_write(row)
        con.sync_exec sql_statement
      else
        # We found the category 'other'
        next
      end
    end
  rescue PG::Error => e
    puts e.message
  ensure
    rows.clear if rows
  end
end

# Putting it all together

# Create the database table for Step 3
postgres.sync_exec(CREATE_TABLE)

# Import the data from the CSV (from Step 2)
system(SEED_BASH_COMMAND)

# Create views for Step 4
postgres.sync_exec(POST_CODE_DETAILS_SQL)
postgres.sync_exec(POST_CODE_SUMMARY_SQL)

# Update category column for Step 5
category_update(postgres)

# Create a view for Step 6
postgres.sync_exec(AGGREGATE_VIEW_SQL)

# Update or export the table for Step 7
export_or_update(postgres)

# All Done
postgres.close

post_code_details_view.sql

-- Step 4
create view post_code_details (post_code, total_places, total_chairs, chairs_pct) as
    select
      post_code,
      count(cafe),
      sum(number_of_chairs),
      (cast(sum(number_of_chairs) as decimal(5))/(select cast(sum(number_of_chairs)as decimal(5)) from street_cafes))
    from street_cafes group by post_code;

post_code_summary_view.sql

create view post_code_summary (place_with_max_chairs, max_chairs) as
    select cafe, number_of_chairs
    from street_cafes where number_of_chairs = (select max(number_of_chairs) from street_cafes);

street_cafes_seed.csv

cafe|street_address|post_code|number_of_chairs|notes|category
All Bar One|27 East Parade|LS1 5BN|20| |
All Bar One|Unit D Electric Press, 4 Millenium Square|LS2 3AD|140| |
Bagel Nash|34 St. Pauls Street|LS1 2AT|14| |
Bagel Nash|18 Swan Street|LS1 6AZ|18| |
Barburrito|62 The Headrow|LS1 8EQ|8| |
Bella Italia|145 Briggate|LS1 6BR|32| |
Becketts Bank (Wetherspoons)|28 - 30 Park Row|LS1 5HU|6| |
Bean on the Run|Fish Street|LS1 6DB|20| |
BHS|49 Boar Lane|LS1 5EL|6| |
Black House Grill|31 - 33 East Parade|LS1 5PS|60| |
Blayds Bar |3-7 Blayds Yard|LS1 4AD|16| |
Browns|70 - 72 The Headrow, The Light|LS1 8EQ|20| |
Byron|9A Lands Lane|LS1 6AW|42| |
Caffé Nero (Albion Place side)|19 Albion Place|LS1 6JS|20| |
Caffe Nero (Albion Street side)|19 Albion Place|LS1 6JS|16| |
Caffe Nero (Bond Street side)|19 Albion Place|LS1 6JS|22| |
Carluccios|5 Greek Street|LS1 5SX|18| |
Cattle Grid|Waterloo House, Assembly Street|LS2 7DB|20| |
Chilli White|Assembly Street|LS2 7DA|51| |
Costa Coffee Albion Place|12A Assembly Street|LS1 6JF|28| |
Costa Coffee Bond Court|2 Bond Court|LS1 2JY|30| |
Costa Coffee Briggate|133 Briggate|LS1 6BR|16| |
Cuthbert Brodrick (Wetherspoons)|99 Portland Crescent|LS1 3HJ|66| |
Gourmet Burger Kitchen|Minerva House, 29 East Parade|LS1 5PS|20| |
Greggs, Briggate|Unit, 7 Central Arcade|LS1 6DX|4| |
Heaven|Lands Lane|LS1 6LB|16| |
Hotel Chocolat|55 Boar Lane|LS1 5EL|12| |
Hourglass|157 - 158 Lower Briggate|LS1 6BG|10| |
Jamie's Italian|35 Park Row|LS1 5JL|32| |
La Bottega Milanese (Bond Court)|2 Bond Court|LS1 2JZ|16| |
La Strega|6 Fish Street|LS1 6DB|12| |
Las Iguanas|3 Cloth Hall St|LS1 2HD|4| |
Leeds Tapped|51 Boar Ln|LS1 5EL|10| |
Little Tokyo|24 Central Rd|LS1 6DE|24| |
Loch Fyne Restaurant|The Old Post Office, 2 City Square|LS1 2ES|72| |
Miller & Carter |56 - 58 The Headrow|LS1 8TL|12| |
Mojo|18 Merrion Street|LS1 6PQ|21| |
Mook|3 - 5 Hirst's Yard|LS1 6NJ|15| |
Mrs Atha's |Central Road|LS1 6DE|12| |
Norman Bar|36 Call Lane|LS1 6DT|24| |
Pasta Romagna|26 Albion Place|LS1 6JS|48| |
Patisserie Valerie|50A Albion Street|LS1 6AD|8| |
Peachy Keens|Electric Press Building|LS2 3AD|96| |
Piccolino|11 - 12 Park Row|LS1 5HD|16| |
Pizza Express|4 Albion Place|LS1 6JL|24| |
Pret a Manger, Bond St|32 Bond St|LS1 5BQ|12| |
Pret a Manger, Lands Lane|6 Lands Lane|LS1 6AW|44| |
Primo's Gourmet Hot Dogs|Unit 12 A/B, The Concourse Corn Exchange|LS1 7BR|16| |
Prohibition|Regents Court, 39A Harrogate Road|LS7 3PD|32| |
Radisson Hotel|1 The Light, The Headrow|LS1 8TL|30| |
Reds Barbecue|1 Cloth Hall Street|LS1 2HD|40| |
Reform|12 - 14 Merrion Street|LS1 6PQ|24| |
Restaurant Bar and Grill|The Old Post Office, 3 City Square |LS1 2AN|152| |
Revolution|41 Cookridge Street|LS2 3AW|84| |
Safran|81 Kirkgate|LS2 7DJ|6| |
San Co Co|12 New Briggate|LS1 6NU|6| |
Sandinista|5 Cross Belgrave Street|LS2 8JP|18| |
Scarbrough Hotel|Bishopgate Street|LS1 5DY|24| |
Slug & Lettuce|14 Park Row|LS1 5HU|14| |
Souvlaki restaurant and Bar|18 Great George Street|LS1 3DW|6| |
Starbucks, Albion Street|48 Albion Street|LS1 6AA|21| |
Starbucks, Briggate|80 Briggate|LS1 6LQ|2| |
Starbucks, Headrow|13 The Headrow|LS1 8EQ|8| |
The Adelphi|3 - 5 Hunslet Road|LS10 1JQ|35| |
The New Conservatory|Albion Place|LS1 6JL|12| |
The Picture House|82 - 90 Merrion Street|LS2 8LW|20| |
The Pit|9 Merrion Street|LS1 6PQ|64|*16 Benches Seating 64|
The White Swan|5 Swan Street|LS1 6LG|28| |
The Wrens Hotel|61A New Briggate|LS2 8JD|20| |
Tiger Tiger|117 Albion St|LS2 8DY|118| |
Town Hall Tavern|17 Westgate |LS1 2RA|16| |
Verve|16 Merrion Street|LS1 6PQ|24| |
Zizzi Restaurant|2 Cloth Hall Street|LS1 2HD|6| |