** DAY 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?
This is another one where we'll need info from the previous query. We need to know the customerid of The Bargain Hunter, since the Ex-boyfriend bought the same item at the same time. But the item was a different color, so we'll also need to look at Products with a color description. I think that's all the actual clues in the text.
What we know:
- The Ex-boyfriend bought an item at Noah's Market the same day as The Bargain Hunter.
- The Bargain Hunter's customerid is 4167
- Colored items are designated in product.sku as 'COL%'
So first, let's find the orders that were placed on the same day as The Bargain Hunter.
SELECT o1.customerid, o1.orderid, o2.customerid, o2.orderid
FROM orders o1
INNER JOIN orders o2 ON o1.customerid <> o2.customerid
AND YEAR(o1.ordered) = YEAR(o2.ordered)
AND MONTH(o1.ordered) = MONTH(o2.ordered)
AND DAY(o1.ordered) = DAY(o2.ordered)
WHERE o1.customerid = 4167
This gives us over 3000 orders. This also shows that we probably need to simplify date searching a little bit. orders.ordered is a datetime datatype. We want to truncate that date to just `YYYY-MM-DD`, and we can do this by simply using CAST(orders.ordered AS date). That will trim off the time part. That way we aren't pulling out each part. We can go back to that if we need to later.
We also need to narrow these orders down to just those items with a color listed. Looking at the data shows us that it's part of products.descr.
/* Get Sherri's orders */
SELECT o.ordered, oi.sku, p.descr, CAST(o.ordered AS date) AS truncDate
FROM orders o
INNER JOIN orders_items oi ON o.orderid = oi.orderid
INNER JOIN products p ON oi.sku = p.sku
AND p.sku LIKE 'COL%'
WHERE o.customerid = 4167
This drops it down to just 4 orders. That should be easy to work with. Now we just need to find other orders placed on that date.
/* Find Orders from same time as Sherri's */
; WITH sherriOrders AS (
SELECT o.ordered, oi.sku, p.descr, CAST(o.ordered AS date) AS truncDate
FROM orders o
INNER JOIN orders_items oi ON o.orderid = oi.orderid
INNER JOIN products p ON oi.sku = p.sku
AND p.sku LIKE 'COL%'
WHERE o.customerid = 4167
)
SELECT c.customerid, c.name, c.phone, o.*, so.*, ABS(DATEDIFF(hour,o.ordered,so.ordered)) AS diffHrs
FROM orders o
INNER JOIN orders_items oi ON o.orderid = oi.orderid
INNER JOIN products p ON oi.sku = p.sku
--AND p.sku LIKE 'COL%'
INNER JOIN sherriOrders so ON oi.sku = so.sku
AND CAST(o.ordered AS date) = so.truncDate
AND o.ordered <= so.ordered
--AND DATEDIFF(hour,o.ordered,so.ordered) < 1 /* Orders less than 1 hour apart. */
INNER JOIN customers c ON o.customerid = c.customerid
WHERE o.customerid <> 4167 /* Exclude Sherri */
/* NO RESULTS */
That's not good. That shouldn't have eliminated everything. There should be other orders that happened at the same time.
After digging at the data, I realized that I was eliminating the data that I actually needed. I was comparing Product SKUs from both orders, but the text says that these items were different colors. They'll have different SKUs, so I need to compare the item name without the colors.
/* Find Orders from same time as Sherri's */
; WITH sherriOrders AS (
SELECT o.ordered, oi.sku, p.descr
, SUBSTRING(p.descr,1,CHARINDEX('(',p.descr)-2) AS prodName
, CAST(o.ordered AS date) AS truncDate
FROM orders o
INNER JOIN orders_items oi ON o.orderid = oi.orderid
INNER JOIN products p ON oi.sku = p.sku
AND p.sku LIKE 'COL%'
WHERE o.customerid = 4167 /* Sherri */
)
SELECT c.customerid, c.name, c.phone --, o.*, oi.*,p.*, so.*
FROM orders o
INNER JOIN customers c ON o.customerid = c.customerid
INNER JOIN orders_items oi ON o.orderid = oi.orderid
INNER JOIN products p ON oi.sku = p.sku
AND p.sku LIKE 'COL%'
INNER JOIN sherriOrders so ON
SUBSTRING(p.descr,1, CASE WHEN CHARINDEX('(',p.descr) IS NULL THEN LEN(p.descr) ELSE CHARINDEX('(',p.descr)-2 END ) = so.prodName /* THIS IS AN INSANE SPLITTING */
AND CAST(o.ordered AS date) = so.truncDate
AND ABS(DATEDIFF( minute, o.ordered,so.ordered )) < 1 /* USE ABS TO ELIMINATE NEGATIVES */
WHERE o.customerid <> 4167
/*
customerid name phone
5783 Carlos Myers 838-335-7157
*/
That one gets us down to just one result. Entering that phone number gives us another right answer.
GO TO DAY 8
BACK TO PART 1
BACK TO PART 2
BACK TO PART 3
BACK TO PART 4
BACK TO PART 5
BACK TO PART 6
