Weekly Puzzle: Use SQL to Find Carmen Sandiego

This week, guest puzzle master Aaron Brager has a scavenger hunt for you.

You'll use SQL to find Carmen Sandiego! She could be anywhere in the world.

How to Play

To get started, fork and clone this GitHub project. This project uses PostgreSQL, a popular database system. You almost certainly already have it installed.

Follow the eight clues in the project's readme.md file to find Carmen. For each clue, you'll need to write a SQL query to hone in on her location.

Submission Instructions

To win, send your SQL queries, their output, and where you think Carmen is, to weeklypuzzle@bloc.io by March 4th 9am PST.

For bonus points, determine Carmen's location using the fewest number of queries, or using interesting SQL syntax.

Last Week's Result Snail Sort

Last week the challenge was to sort an associative array in the pattern of a snail:

The "Who's on First" award

Goes to Daniel Rassiner who completed the puzzle in 2 hours.
http://www.codewars.com/kata/reviews/521d7f76123ebbb78d00006d/groups/56b5867502a303067e000733

def snail(array)

  array_amount = array.size - 1
  total_loops = (array.size + array.size) - 2
  answer = array[0]
  x = 0
  y = array_amount
  index = 1
  what_to_pick = ["r", "d", "l", "u"]

  total_loops.times do

    case what_to_pick[index]
    when "r"
      array_amount.times do
        y += 1
        answer << array[x][y]
      end
    when "d"
      array_amount.times do
        x += 1
        answer << array[x][y]  
      end
    when "l"
      array_amount.times do
        y -= 1
        answer << array[x][y]
      end
    when "u"
      array_amount.times do
        x -= 1
        answer << array[x][y]
      end
      index = -1
    end

    if (index % 2 == 0)
      array_amount -= 1
    end
    index += 1

  end
  answer
end  

The "Loopty Loop" award

Goes to Fong Fan for his multi-loop based solution:

http://www.codewars.com/kata/reviews/521d7f76123ebbb78d00006d/groups/56b6d77281290cdc410005e3

def snail(array)  
  final_array = []
  xMin = 0
  yMin = 0
  xMax = array.length - 1
  yMax = xMax

  # Base Cases
  return final_array if array[0].length == 0
  if array[0].length == 1
    final_array << array[0][0]
    return final_array
  end

  # Clockwise Loop Over All Four Sides
  while (xMin <= xMax) && (yMin <= yMax)
    # Traverse top side
    for y in yMin..yMax
      final_array << array[xMin][y]
    end
    xMin = xMin + 1

    # Traverse right side
    for x in xMin..xMax
      final_array << array[x][yMax]
    end
    yMax = yMax - 1

    # Traverse bottom side
    for y in (yMax).downto(yMin)
      final_array << array[xMax][y]
    end
    xMax = xMax - 1

    # Traverse left side
    for x in (xMax).downto(xMin)
      final_array << array[x][yMin]
    end
    yMin = yMin + 1
  end

  return final_array
end  

The "Jony Ive Minimalism" award

goes to Aaron Brager for his tiny solution.

def snail(array)  
  result = []

  while !array.empty? do
    result << array.shift
    array = array.transpose.reverse
  end

  result.flatten
end  

The key to this solution is the ruby transpose method which allows you to rotate the entire dataset by turning the columns into rows.