"This is an exercise to gauge your familiarity with coding and think through analysis implications. You'll find a prompt and two columns below: 'Coding' and 'Questions'. For the 'Coding' portion please submit the SQL query you would run to figure out the answer. The tables are spelled out in the subsequent tabs and are populated with both a definition of the table/columns and SAMPLE data. The 'Questions' portion asks you to think through the implications of a given result. Feel free to submit in whatever format you're most comfortable with."
You work for J.Crew. J.Crew has just acquired Madewell and you're working with a team that is trying to understand the Madewell business and opportunities to cross-sell to loyalty members.
I've used pandas and pandasql to load the sample databases, augment them with a little more mock data and ensure my queries are working. I've written the MySQL/PostgreSQL versions of the queries as commented-out code cells within this notebook, and I have also saved these to individual .sql files. These should be considered my final answers, though the SQLite queries in the notebook show my experimentation process and the results of running these queries with the provided sample data. Please note that SQLlite has some subtle syntax differences from MySQL.
Answers to the Analysis Questions are located in this PDF file and also in the notebook.
