shoesCodeFor · November 13, 2018 20:09 · Nov 13, 2018
diff --git a/aggregate_view_categories.sql b/aggregate_view_categories.sql
@@ -0,0 +1,7 @@
+-- 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;
diff --git a/category_update.rb b/category_update.rb
@@ -0,0 +1,51 @@
+#!/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
diff --git a/category_update_spec.rb b/category_update_spec.rb
@@ -0,0 +1,34 @@
+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
diff --git a/concat_and_write.rb b/concat_and_write.rb
@@ -0,0 +1,10 @@
+#!/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
diff --git a/concat_and_write_spec.rb b/concat_and_write_spec.rb
@@ -0,0 +1,10 @@
+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
diff --git a/create_db.sql b/create_db.sql
@@ -0,0 +1,11 @@
+-- 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;
diff --git a/create_table_street_cafes.sql b/create_table_street_cafes.sql
@@ -0,0 +1,15 @@
+-- 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
+);
diff --git a/export_and_drop.rb b/export_and_drop.rb
@@ -0,0 +1,16 @@
+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
+
diff --git a/export_and_drop_spec.rb b/export_and_drop_spec.rb
@@ -0,0 +1,10 @@
+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
diff --git a/export_output.csv b/export_output.csv
@@ -0,0 +1,28 @@
+Barburrito,62 The Headrow,LS1 8EQ,8,LS1 Small
+Becketts Bank (Wetherspoons),28 - 30 Park Row,LS1 5HU,6,LS1 Small
+BHS,49 Boar Lane,LS1 5EL,6,LS1 Small
+"Greggs, Briggate","Unit, 7 Central Arcade",LS1 6DX,4,LS1 Small
+Hourglass,157 - 158 Lower Briggate,LS1 6BG,10,LS1 Small
+Las Iguanas,3 Cloth Hall St,LS1 2HD,4,LS1 Small
+Leeds Tapped,51 Boar Ln,LS1 5EL,10,LS1 Small
+Patisserie Valerie,50A Albion Street,LS1 6AD,8,LS1 Small
+Safran,81 Kirkgate,LS2 7DJ,6,LS2 Small
+San Co Co,12 New Briggate,LS1 6NU,6,LS1 Small
+Souvlaki restaurant and Bar,18 Great George Street,LS1 3DW,6,LS1 Small
+"Starbucks, Briggate",80 Briggate,LS1 6LQ,2,LS1 Small
+"Starbucks, Headrow",13 The Headrow,LS1 8EQ,8,LS1 Small
+Zizzi Restaurant,2 Cloth Hall Street,LS1 2HD,6,LS1 Small
+Barburrito,62 The Headrow,LS1 8EQ,8,LS1 Small
+Becketts Bank (Wetherspoons),28 - 30 Park Row,LS1 5HU,6,LS1 Small
+BHS,49 Boar Lane,LS1 5EL,6,LS1 Small
+"Greggs, Briggate","Unit, 7 Central Arcade",LS1 6DX,4,LS1 Small
+Hourglass,157 - 158 Lower Briggate,LS1 6BG,10,LS1 Small
+Las Iguanas,3 Cloth Hall St,LS1 2HD,4,LS1 Small
+Leeds Tapped,51 Boar Ln,LS1 5EL,10,LS1 Small
+Patisserie Valerie,50A Albion Street,LS1 6AD,8,LS1 Small
+Safran,81 Kirkgate,LS2 7DJ,6,LS2 Small
+San Co Co,12 New Briggate,LS1 6NU,6,LS1 Small
+Souvlaki restaurant and Bar,18 Great George Street,LS1 3DW,6,LS1 Small
+"Starbucks, Briggate",80 Briggate,LS1 6LQ,2,LS1 Small
+"Starbucks, Headrow",13 The Headrow,LS1 8EQ,8,LS1 Small
+Zizzi Restaurant,2 Cloth Hall Street,LS1 2HD,6,LS1 Small
diff --git a/gsc_db_script.rb b/gsc_db_script.rb
@@ -0,0 +1,169 @@
+#!/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
diff --git a/post_code_details_view.sql b/post_code_details_view.sql
@@ -0,0 +1,8 @@
+-- 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;
diff --git a/post_code_summary_view.sql b/post_code_summary_view.sql
@@ -0,0 +1,3 @@
+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);
diff --git a/street_cafes_seed.csv b/street_cafes_seed.csv
@@ -0,0 +1,74 @@
+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| |
No results found