Data analysis and reports
QUESTION
QUESTION 1
Write a SQL query (MUST using Postgres or SQLite syntax – http://www.sqlite.org/index.html) on the orders table (orders.csv) that shows:
1. What % of users placed more than 1 order in the first 28 days
2. what % of users have an average days between order over 28 days
Note: An order is considered to be in the user’s first 29 days if it happened before OR on first_date + days(28). E.g. if the user’s first order was on 1/1/2018, an order on 1/29/18 would still count as a first_29_day_order but an order on 1/30/18 would not.
Process: Include your SQL code and answers in a single txt document.
QUESTION 2
Background on the Data:
We have a system that allows apps to send receipt alerts to the shopper in 3 channels (banner, push, sidebar). We log two separate events into a table called alerts (alerts.csv):
1. Whenever a shopper sees an alert (impression or ‘imp’)
2. Whenever a shopper clicks on an alert (‘clk’)
The table contains data for a single day. This logging is new and hasn’t been vetted yet. Trust nothing, callout bad data, and work to clean the data as necessary to get the best answer.
Columns in the table:
user_id: numeric, unique and persistent id for each shopper.
group_id: numeric, unique and persistent id for each store. Each userid belongs to exactly one group. app_id: numeric, unique and persistent id for each app
event: string with values ‘imp’ (as in impression, so it was seen) or ‘clk’ (the shopper interacted with it) primary_browser: string, the browser the user was using
alert_type: string, alert was in a banner/sidebar/push eventtime: the timestamp of when the interaction happened
Questions:
1. What is the best performing alert type?
2. What apps are the best and worst performing?
3. I’m curious about what the first alert a group clicked on in this day? For each alert_type, compute how many groups clicked an alert of that type as their first alert in a day.
4. What other research, experiments, or approaches around alerts would you recommend to improve engagement of the product
Sample Solutions
Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Phasellus hendrerit. Pellentesque aliquet nibh nec urna. In nisi neque, aliquet vel, dapibus id, mattis vel, nisi. Sed pretium, ligula sollicitudin laoreet viverra, tortor libero sodales leo, eget blandit nunc tortor eu nibh. Nullam mollis. Ut justo. Suspendisse potenti.
Get sample solution
Sed egestas, ante et vulputate volutpat, eros pede semper est, vitae luctus metus libero eu augue. Morbi purus libero, faucibus adipiscing, commodo quis, gravida id, est. Sed lectus. Praesent elementum hendrerit tortor. Sed semper lorem at felis. Vestibulum volutpat, lacus a ultrices sagittis, mi neque euismod dui, eu pulvinar nunc sapien ornare nisl. Phasellus pede arcu, dapibus eu, fermentum et, dapibus sed, urna.
Morbi interdum mollis sapien. Sed ac risus. Phasellus lacinia, magna a ullamcorper laoreet, lectus arcu pulvinar risus, vitae facilisis libero dolor a purus. Sed vel lacus. Mauris nibh felis, adipiscing varius, adipiscing in, lacinia vel, tellus. Suspendisse ac urna. Etiam pellentesque mauris ut lectus. Nunc tellus ante, mattis eget, gravida vitae, ultricies ac, leo. Integer leo pede, ornare a, lacinia eu, vulputate vel, nisl.
Suspendisse mauris. Fusce accumsan mollis eros. Pellentesque a diam sit amet mi ullamcorper vehicula. Integer adipiscing risus a sem. Nullam quis massa sit amet nibh viverra malesuada. Nunc sem lacus, accumsan quis, faucibus non, congue vel, arcu. Ut scelerisque hendrerit tellus. Integer sagittis. Vivamus a mauris eget arcu gravida tristique. Nunc iaculis mi in ante. Vivamus imperdiet nibh feugiat est.
Ut convallis, sem sit amet interdum consectetuer, odio augue aliquam leo, nec dapibus tortor nibh sed augue. Integer eu magna sit amet metus fermentum posuere. Morbi sit amet nulla sed dolor elementum imperdiet. Quisque fermentum. Cum sociis natoque penatibus et magnis xdis parturient montes, nascetur ridiculus mus. Pellentesque adipiscing eros ut libero. Ut condimentum mi vel tellus. Suspendisse laoreet. Fusce ut est sed dolor gravida convallis. Morbi vitae ante. Vivamus ultrices luctus nunc. Suspendisse et dolor. Etiam dignissim. Proin malesuada adipiscing lacus. Donec metus. Curabitur gravida