# _solutions

query:
  -- 1: The Investigator

  SELECT name, phone,
    REPLACE( REPLACE( REPLACE( REPLACE(
    REPLACE( REPLACE( REPLACE(
    REPLACE( REPLACE( REPLACE( REPLACE(
    REPLACE( REPLACE( REPLACE(
    REPLACE( REPLACE( REPLACE(
    REPLACE( REPLACE( REPLACE(
    REPLACE( REPLACE( REPLACE(
    REPLACE( REPLACE( REPLACE(
    REPLACE(LOWER(name), ' ', ''),
    'a', '2'), 'b', '2'), 'c', '2'),
    'd', '3'), 'e', '3'), 'f', '3'),
    'g', '4'), 'h', '4'), 'i', '4'),
    'j', '5'), 'k', '5'), 'l', '5'),
    'm', '6'), 'n', '6'), 'o', '6'),
    'p', '7'), 'q', '7'), 'r', '7'), 's', '7'),
    't', '8'), 'u', '8'), 'v', '8'),
    'w', '9'), 'x', '9'), 'y', '9'), 'z', '9') AS phonespell,
    REPLACE(phone, '-', '') AS barephone
  FROM customers
  WHERE instr(phonespell, barephone) > 0


query:
  -- 2. The Contractor

  SELECT c.name, c.phone, o.ordered, p.desc
  FROM customers c
    JOIN orders o on c.customerid = o.customerid
    JOIN orders_items i on o.orderid = i.orderid
    JOIN products p on i.sku = p.sku
  WHERE strftime('%Y', o.ordered) = '2017' AND c.name LIKE 'D% S%'
  GROUP BY c.name, c.phone, o.ordered
  HAVING SUM(CASE WHEN desc LIKE '%coffee%' THEN 1 ELSE 0 END) > 0
     AND SUM(CASE WHEN desc LIKE '%bagel%' THEN 1 ELSE 0 END) > 0


query:
  -- 3. The Neighbor

  SELECT DISTINCT c.name, c.birthdate, c.address, c.citystatezip, c.phone
  FROM customers c
  JOIN orders o ON c.customerid = o.customerid
  JOIN orders_items i ON o.orderid = i.orderid
  JOIN products p ON i.sku = p.sku
  WHERE c.citystatezip LIKE '%Queens Village, NY 11427'
    AND strftime('%Y', c.birthdate) % 12 = 11
    AND CAST(strftime('%m%d', c.birthdate) AS int) BETWEEN 921 AND 1019


query:
  -- 4. The Early Bird

  SELECT c.name, c.phone, count(*) as num_orders
  FROM customers c
  JOIN orders o on c.customerid = o.customerid
  JOIN orders_items i on o.orderid = i.orderid
  WHERE i.sku like 'BKY%'
      AND CAST(strftime('%H', o.ordered) as int) <= 5
  GROUP BY c.name
  ORDER BY num_orders DESC
  LIMIT 1


query:
  -- 5. The Cat Lady

  SELECT c.name, c.phone, sum(i.qty) as total_items
  FROM customers c
      JOIN orders o on c.customerid = o.customerid
      JOIN orders_items i on o.orderid = i.orderid
      JOIN products p on i.sku = p.sku
  WHERE p.desc like '%senior%cat%'
  GROUP BY c.name, c.phone
  ORDER BY total_items desc
  LIMIT 1


query:
  -- 6. The Bargain Hunter

  SELECT name, phone, sum(wholesale_cost - unit_price) as discount
  FROM customers c
      JOIN orders o on c.customerid = o.customerid
      JOIN orders_items i on o.orderid = i.orderid
      JOIN products p on i.sku = p.sku
  GROUP BY name, phone
  ORDER BY discount DESC
  LIMIT 1


query:
  -- 7. The Meet Cute

  WITH p6_orders as (SELECT *
  FROM customers c
      JOIN orders o on c.customerid = o.customerid
      JOIN orders_items i on o.orderid = i.orderid
      JOIN products p on i.sku = p.sku
  WHERE 8884 = c.customerid)

  SELECT c.name, c.phone,
      case
          when instr(p.desc, '(') = 0 then p.desc
          else substr(p.desc, 1, instr(p.desc, '(') - 1)
      end as stripped_desc,
      case
          when instr(p6.desc, '(') = 0 then p6.desc
          else substr(p6.desc, 1, instr(p6.desc, '(') - 1)
      end as p6_stripped_desc
  FROM customers c
      JOIN orders o on c.customerid = o.customerid
      JOIN orders_items i ON o.orderid = i.orderid
      JOIN p6_orders p6
      JOIN products p ON i.sku = p.sku
  WHERE abs(strftime('%s', o.ordered) - strftime('%s', p6.ordered)) <= 150
      AND stripped_desc = p6_stripped_desc
      AND p6.customerid != c.customerid


query:
  -- 8. The Collector

  SELECT c.name, c.phone, count(distinct i.sku) as col_count
  FROM customers c
      JOIN orders o on c.customerid = o.customerid
      JOIN orders_items i on o.orderid = i.orderid
  WHERE sku like 'COL%'
  GROUP BY name, phone
  ORDER BY col_count DESC
  LIMIT 1


# _conversations

contact: 516-821-8454
text:
  ## The Investigator

  You called Alex, and Alex gave you the lowdown on the terminal.

  "It's just [SQLite](https://www.sqlite.org/)," he said. "It's not that hard to figure out.  I even made a nice little interface for it--everything you need to know should be in the Help tab.  Anyway I have to go, I'm trying to solve the latest Advent of Code puzzle using CSS."

  Alex hung up just as Sarah brought a cashier into the office.

  She said, "Joe here says that one of our customers is a skilled private investigator."

  Joe nodded, "They showed me their business card, and that's what it said. Skilled Private Investigator. And their phone number was their last name spelled out.
  I didn't know what that meant, but apparently before there were smartphones, people had to remember phone numbers or write them down.
  If you wanted a phone number that was easy-to-remember, you could get a number that spelled something using the letters printed on the phone buttons: like 2 has "ABC", and 3 "DEF", etc.
  And I guess this person had done that, so if you dialed the numbers corresponding to the letters in their name, it would call their phone number!

  "I thought that was pretty cool.  But I don't remember their name, or anything else about them for that matter.  I couldn't even tell you if they were male or female."

  Sarah said, "This person seems like they are skilled at investigation.
  I need them to find Noah's rug before the Hanukkah dinner.
  I don't know how to contact them, but apparently they shop here at Noah's Market."

  "So can you find this investigator's phone number?"


contact: 767-365-7269
text:
  ## The Contractor

  Thanks to your help, Sarah called the investigator that afternoon.
  The investigator went directly to the cleaners to see if they could get any more information about the unclaimed rug.

  While they were out, Sarah said, "I tried cleaning the rug myself, but there was this snail on it that always seemed to leave a trail of slime behind it.  I spent a few hours cleaning it, and the next day the slime trail was back."

  When the investigator returned, they said, "Apparently, this cleaner had a special projects program, where they outsourced challenging cleaning projects to industrious contractors.
  As they're right across the street from Noah's, they usually talked about the project over coffee and bagels at Noah's before handing off the item to be cleaned.
  The contractors would pick up the tab and expense it, along with their cleaning supplies.

  "So this rug was apparently one of those special projects.  The claim ticket said '2017 DS'.  '2017' is the year the item was brought in, and 'DS' is the initials of the contractor.

  "But they stopped outsourcing a few years ago, and don't have contact information for any of these workers anymore."

  Sarah first seemed hopeless, and then glanced at the terminal you were just trying to get away from.
  She said, "I know it's a long shot, but is there any chance you could find their phone number?"


contact: 838-351-0370
text:
  ## The Neighbor

  Sarah and the investigator were very impressed with your data skills, as you were able to figure out the phone number of the contractor.
  They called up the cleaning contractor straight away and asked about the rug.

  "Oh, yeah, I did some special projects for them a few years ago.
  I remember that rug unfortunately.
  I managed to clean one section, which revealed a giant spider that startled me whenever I tried to work on it.

  "I already had a fear of spiders before this, but this spider was so realistic that I had a hard time making any more progress.
  I kept expecting the cleaners would call for the rug, but they never did.
  I felt so bad about it, I couldn't face them, and of course they never gave me another project.

  "At last I couldn't deal with the rug taking up my whole bathtub, so I gave it to this guy who lived in my neighborhood.
  He said that he was naturally balanced because he was a Libra born in the year of the Goat, so maybe he was able to clean it.

  "I don't remember his name.
  Last time I saw him, he was leaving the subway and carrying a bag from Noah's.
  I swore I saw a spider on his hat."

  Can you find the phone number of the person that the contractor gave the rug to?


contact: 914-594-5535
text:
  ## The Early Bird

  The investigator called the phone number you found and left a message, and a man soon called back:

  "Wow, that was years ago!  It was quite an elegant tapestry.

  "It took a lot of patience, but I did manage to get the dirt out of one section, which uncovered a superb owl.
  I put it up on my wall, and sometimes at night I swear I could hear the owl hooting.

  "A few weeks later my bike chain broke on the way home, and I needed to get it fixed before work the next day.
  Thankfully, this woman I met on Tinder came over at 5am with her bike chain repair kit and some pastries from Noah's.
  Apparently she liked to get up before dawn and claim the first pastries that came out of the oven.

  "I didn't have any money or I would've paid her for her trouble.
  She really liked the tapestry, though, so I wound up giving it to her.

  "I don't remember her name or anything else about her."

  Can you find the bicycle fixer's phone number?


contact: 716-789-4433
text:
  ## The Cat Lady

  "Yes, I did have that tapestry for a little bit.
  I even cleaned a blotchy section that turned out to be a friendly koala.

  "But it was still really dirty, so when I was going through a Marie Kondo phase, I decided it wasn't sparking joy anymore.

  "I listed it on Freecycle, and a woman came to pick it up.
  She was wearing a 'Noah's Market' sweatshirt, and it was just covered in cat hair.
  When I suggested that a clowder of cats might ruin such a fine tapestry, she looked at me funny.
  She said "I only have ten or eleven cats, and anyway they are getting quite old now, so I doubt they'd care about some old rug."

  "It took her 20 minutes to stuff the tapestry into some plastic bags she brought because it was raining.  I spent the evening cleaning my apartment."

  What's the phone number of the woman from Freecycle?


contact: 347-835-2358
text:
  ## The Bargain Hunter

  "Why yes, I did have that rug for a little while in my living room!  My cats can't see a thing but they sure chased after the squirrel on it like it was dancing in front of their noses.

  "It was a nice rug and they were surely going to ruin it, so I gave it to my cousin, who was moving into a new place that had wood floors.

  "She refused to buy a new rug for herself--she said they were way too expensive.  She's always been very frugal, and she clips every coupon and shops every sale at Noah's Market.
  In fact I like to tease her that Noah actually loses money whenever she comes in the store.

  "I think she's been taking it too far lately though.  Once the subway fare increased, she stopped coming to visit me.  And she's really slow to respond to my texts.  I hope she remembers to invite me to the family reunion next year."

  Can you find her cousin's phone number?


contact: 838-295-7143
text:
  ## 7. The Meet Cute

  "Oh that tapestry, with the colorful toucan on it!  I'll tell you what happened to it.

  "One day, I was at Noah's Market, and I was just about to leave when someone behind me said 'Miss!  You dropped something!'

  "Well I turned around to see this cute guy holding an item I had bought.
  He said, 'I got the same thing!'  We laughed about it and wound up swapping items because I wanted the color he got.
  We had a moment when our eyes met and my heart stopped for a second.
  I asked him to get some food with me and we spent the rest of the day together.

  "Before long I moved into his place, but the romance faded quickly, as he wasn't the prince I imagined.
  I left abruptly one night, forgetting the tapestry on his wall. But by then, it symbolized our love, and I wanted nothing more to do with it.
  For all I know, he still has it."

  Can you figure out her ex-boyfriend's phone number?


contact: 516-544-4187
text:
  ## The Collector

  "Oh that damned woman! She moved in, clogged my bathtub, left her coupons all over the kitchen, and then just vanished one night without leaving so much as a note.

  Except she did leave behind that nasty carpet.  I spent months cleaning one corner, only to discover a snake hiding in the branches!  I knew then that she was never coming back, and I had to get it out of my sight.

  "Well, I don't have any storage here, and it didn't seem right to sell it, so I gave it to my sister.
  She wound up getting a newer and more expensive carpet, so she gave it to an acquaintance of hers who collects all sorts of junk.
  Apparently he owns an entire set of Noah's collectibles!
  He probably still has the carpet, even.

  "My sister is away for the holidays, but I can have her call you in a few weeks."

  The family dinner is tonight!  Can you find the collector's phone number in time?


contact: 516-638-9966
text:
  ## Epilogue

  "Oh yes, that magnificant Persian carpet!
  An absolute masterpiece, with a variety of interesting animals congregating around a Tree of Life.
  As a collector, I couldn't believe when it fell into my lap.

  "A friend of mine had taken it off her brother's hands, and she didn't know what to do with it.
  I saw her one day, and she was about to put an old rug out at the curb.
  It looked like it had been through a lot, but it was remarkably not that dirty.
  It still took quite a bit of effort and no small amount of rug cleaner, but ultimately I managed to get the last bits of grime out of it.

  "I actually live right down the street from Noah's Market--I'm a huge fan and I shop there all the time!
  I even have a one-of-a-kind scale model of Noah's Ark that makes a complete set of Noah's collectibles.

  "I would love for Noah to have his rug once again to enjoy."

  <a href='#endgame' onclick="event.stopPropagation(); window.showArt()" id='showart'>See Noah's entire tapestry</a>


contact: help
text:
  # How to use this interface

  <div class="helpnote">
  Note: We are <a href="/damgoods/" title="Damaged Goods">currently working on a new game</a>.
  Please help us beta-test this client with Noah's Rug in advance of the release!
  If you find any issues, please report them to <a href="mailto:bugs@whereinthedata.com">bugs@whereinthedata.com</a>.
  </div>

  <a href="#" onclick="event.preventDefault(); window.resetLog()">Click to reset the game and start over.</a>

  ## Queries

  This is a basic interface to a SQLite database.
  Type a SQL query in the textarea and press the "Run Query" button (or Ctrl+Enter), and it will display the first 30 rows into the results window.

  Successful queries are saved locally; press the Up or Down buttons (or Ctrl+Up/Ctrl+Down) to load the previous or next saved query.
  Press the [x] button to remove a saved query.
  Click on the "Query" tab to show the most recent query.

  You can also download the dataset and query it using your own custom tools.

  ## Clues

  When a column name in a query contains "phone", each value in that column is a clickable links to a pre-recorded conversation with that person into the results window.
  These clues are saved locally, like queries; press the Left or Right buttons (or Ctrl+Left/Ctrl+Right) to load the previous or next saved clue.
  Press the [x] button in the clue results window to remove a saved clue.
  Click on the "Clues" tab to show the most recent clue.

  ## Art

  Click on the "Art" tab to show the in-progress Art piece.  Each saved clue reveals another section of the Art.


contact: intro
text:
  ## Noah's Market

  Welcome to "Noah's Market", a bustling mom-and-pop everything store in Manhattan. In recent years it’s become quite an operation, but Noah and family still own and manage it.

  This morning, while waiting for your breakfast bagel, your Aunt Sarah pulled you aside in a hustle.

  "You know how Grandpa Noah has been talking recently about that rug we used to have?"

  She looked over at Noah, who was talking to a customer: "Such a beautiful carpet, with the most intricate design! I miss having it in my den. It has this vibrant beehive buzzing in the corner…"

  Sarah explained, "He entrusted me with that rug when he was remodeling his den a few years ago. It was so old and filthy, that I sent it to the cleaners, but then I completely forgot about it. Now, with him retiring and me taking over the store, he wants it back. So yesterday I freaked out and combed my apartment, and I finally found a claim ticket saying, 'All items must be picked up within 90 days.' At the cleaners, they didn't have the rug, just the other half of the ticket."

  Sarah added, "I need to find that rug before your grandpa comes over on the last night of Hanukkah.  I have an idea involving a customer in our database--we can look up customers by name, of course, but this one is a little tricky because I don't know their name exactly.  So I called Alex, you know how he set up the database when he was just a freshman?  But now that he's working for a big corporation, little Alexander Carpenter has no time to help his mother."

  She brings you into the back office and shows you a computer terminal.

  "But I was thinking, maybe you could do it?  I mean here is the terminal where Alex did all of his stuff with the database.  I can never find the time to figure it out, so you'll have to call Alex to learn how to use it."

  Sarah sighs and goes to talk with one of the cashiers.

  Can you find your cousin's phone number?


# _art

id: off
type: frame
tags:
  516-821-8454 767-365-7269 838-351-0370 914-594-5535 716-789-4433 347-835-2358 838-295-7143 516-544-4187 516-638-9966


text: ⎝
color: 202
frame: day0f_0 day1f_0 day2f_0 day3f_0 day4f_0 day5f_0 day6f_0 day7f_0 day8f_0
tags:
  516-821-8454f 767-365-7269f 838-351-0370f 914-594-5535f 716-789-4433f 347-835-2358f 838-295-7143f 516-544-4187f 516-638-9966f


