# Set up feedback system
from learntools.core import binder
binder.bind(globals())
from learntools.sql_advanced.ex4 import *
print("Setup Complete")

"""
Explanation: Introduction
Now, you'll use what you learned in the previous tutorial to improve the efficiency of several queries.
Before you get started, run the following cell to set everything up.
End of explanation
"""


# Fill in your answer
query_to_optimize = ____

# Check your answer
q_1.check()

# Lines below will give you a hint or solution code
#_COMMENT_IF(PROD)_
q_1.hint()
#_COMMENT_IF(PROD)_
q_1.solution()

#%%RM_IF(PROD)%%
query_to_optimize = 3

q_1.check()

"""
Explanation: Exercises
1) You work for Pet Costumes International.
You need to write three queries this afternoon. You have enough time to write working versions of all three, but only enough time to think about optimizing one of them.  Which of these queries is most worth optimizing?


A software engineer wrote an app for the shipping department, to see what items need to be shipped and which aisle of the warehouse to go to for those items. She wants you to write the query. It will involve data that is stored in an orders table, a shipments table and a warehouseLocation table. The employees in the shipping department will pull up this app on a tablet, hit refresh, and your query results will be shown in a nice interface so they can see what costumes to send where.


The CEO wants a list of all customer reviews and complaints… which are conveniently stored in a single reviews table. Some of the reviews are really long… because people love your pirate costumes for parrots, and they can’t stop writing about how cute they are.


Dog owners are getting more protective than ever. So your engineering department has made costumes with embedded GPS trackers and wireless communication devices. They send the costumes’ coordinates to your database once a second. You then have a website where owners can find the location of their dogs (or at least the costumes they have for those dogs). For this service to work, you need a query that shows the most recent location for all costumes owned by a given human. This will involve data in a CostumeLocations table as well as a CostumeOwners table.


So, which of these could benefit most from being written efficiently?  Set the value of the query_to_optimize variable below to one of 1, 2, or 3.  (Your answer should have type integer.)
End of explanation
"""


# Line below will give you a hint
#_COMMENT_IF(PROD)_
q_2.hint()

# View the solution (Run this code cell to receive credit!)
q_2.solution()

"""
Explanation: 2) Make it easier to find Mitzie!
You have the following two tables:

The CostumeLocations table shows timestamped GPS data for all of the pet costumes in the database, where CostumeID is a unique identifier for each costume.  
The CostumeOwners table shows who owns each costume, where the OwnerID column contains unique identifiers for each (human) owner.  Note that each owner can have more than one costume!  And, each costume can have more than one owner: this allows multiple individuals from the same household (all with their own, unique OwnerID) to access the locations of their pets' costumes.
Say you need to use these tables to get the current location of one pet in particular: Mitzie the Dog recently ran off chasing a squirrel, but thankfully she was last seen in her hot dog costume!
One of Mitzie's owners (with owner ID MitzieOwnerID) logs into your website to pull the last locations of every costume in his possession.  Currently, you get this information by running the following query:
sql
WITH LocationsAndOwners AS 
(
SELECT * 
FROM CostumeOwners co INNER JOIN CostumeLocations cl
   ON co.CostumeID = cl.CostumeID
),
LastSeen AS
(
SELECT CostumeID, MAX(Timestamp)
FROM LocationsAndOwners
GROUP BY CostumeID
)
SELECT lo.CostumeID, Location 
FROM LocationsAndOwners lo INNER JOIN LastSeen ls 
    ON lo.Timestamp = ls.Timestamp AND lo.CostumeID = ls.CostumeID
WHERE OwnerID = MitzieOwnerID
Is there a way to make this faster or cheaper?
End of explanation
"""


