Customer Engineer Technical Screen
📈

Customer Engineer Technical Screen

Context

Avenue connects to companies' Data Warehouses and alerts and built with SQL. A lot of the onboarding, expansion, and support work boils down to helping our customers understand their (often messy) data, build metrics, create alerts, etc.

Customer Engineering @ Avenue

Questions

Please answer the following questions with SQL solution along with a 1-3 sentence description. Submit your answers, in any format, to justin@useavenue.com

Problem 1

Assume you have the below tables on user actions. Write a query to get the active user retention by month.

user_actions

column_nametype
user_id
integer
event_id
string ("sign-in", "like", "comment")
timestamp
datetime

Problem 2

Assume you are given the below tables for trades and users. Write a query to list the top 3 cities which had the highest number of completed orders.

trades

column_nametype
order_id
integer
user_id
integer
symbol
string (e.g. "NFLX", "FB", etc.)
price
float
quantity
integer
side
string ("buy", "sell")
status
string ("complete", "cancelled")
timestamp
datetime

users

column_nametype
user_id
integer
city
string
email
string
signup_date
datetime

Problem 3

Assume you have the below events table on app analytics. Write a query to get the click-through rate per app in 2019.

events

column_nametype
app_id
integer
event_id
string ("impression", "click")
timestamp
datetime

Problem 4

Assume you are given the below table for spending activity by product type. Write a query to calculate the cumulative spend for each product over time in chronological order.

total_transactions

column_nametype
order_id
integer
user_id
integer
product_id
string
spend
float
date
datetime

Problem 5

Assume you have the below tables on sessions that users have, and a users table. Write a query to get the active user count of daily cohorts, i.e. the counts of users registered each day.

sessions

column_nametype
session_id
integer
user_id
integer
date
datetime

users

column_nametype
user_id
integer
email
string
date
datetime