Project: SQL Skills Assessment

Summary:

This is a project I completed as part of an interview process. It's an assessment of SQL skills and database savvy using fictional conceptual data. The prompt and background information is provided below. The deliverable was a written document, shown below.

The Prompt

There is no sample data for this dataset - the intention here is to assess your ability to think about relational databases, in regards to extracting data for specific business needs, and write the code as if this dataset existed.
The intention is NOT to test your knowledge of SQL syntax, but it should be reasonably accurate in respect to whichever SQL dialect you choose to use.

The Context and Data Tables

Imagine you work for a popular international ice cream shop chain that is able to capture data on every transaction.

Purchases Table
Fields:
∙ Id - unique identifier
∙ User_id – foreign key to users table
∙ Price – integer in cents, for actual price paid
∙ Purchase_timestamp (UTC)
∙ Item_id – foreign key to items table

Users Table

Fields:
∙ User_id - unique identifier
∙ Country
∙ Birth Date
∙ Acquired_timestamp (UTC)
∙ UserSource (= Acquisition Channel)
∙ CostToAcquire - Price paid to acquire the customer, in dollars

ItemsPurchased Table
Fields:
∙ Id - unique identifier

∙ Flavor
∙ Regular_price – integer in cents
∙ Discount_price – integer in cents

Questions and Answers

1. Create SQL to determine total revenue by month.

DROP TEMPORARY TABLE if EXISTS revenuedate;

CREATE TEMPORARY TABLE revenuedate

SELECT

Price,

DATE(Purchase_timestamp) as purchasedate

FROM

purchases

GROUP BY purchasedate

WHERE

1=1

;

SELECT

SUM(Price) as revenue,

MONTH(purchasedate) as purchasemonth

FROM

Revenuedate

WHERE

1=1

GROUP BY purchasemonth

;