cli.rb

def initialize
  @conn = PG.connect(dbname: 'restaurants')
end

def unique_dishes
  response = @conn.exec(%(SELECT name FROM dishes ORDER BY name ASC;)).to_a
  query_to_table(response)
end

def user_distribution(group_value)
  query = %(SELECT #{group_value}, COUNT(*) AS count,
    CAST(COUNT(#{group_value}) AS FLOAT) * 100/(SELECT COUNT(id) FROM users) AS percentage
    FROM users
    GROUP BY #{group_value}
    ORDER BY count DESC;
  )
  response = @conn.exec(query).to_a
  response.each { |x| x["percentage"] = x["percentage"].to_f.round(2) }
  query_to_table(response)
end

def top_by_visitors
  query = %(
    SELECT r.name, COUNT(v.rest_id) as count
    FROM visits AS v
      JOIN restaurants r ON v.rest_id = r.id
    GROUP BY r.name
    ORDER BY count DESC
    LIMIT 10;
  )
  response = @conn.exec(query).to_a
  query_to_table(response)
end