Katkat
Back

Write Transaction Analytics SQL Queries

ZooxData Engineer · Technical Screen
MediumData EngineerTechnical ScreenCoding & Algorithms

Last updated

This question evaluates proficiency in SQL for analytical queries, covering aggregation, time-based filtering, joins between transactions and vendor metadata, and the use of ranking/window functions for top-N calculations.

You are given a credit-card transaction dataset with the following tables.

transactions

columntypedescription
transaction_idintegerUnique ID for a transaction.
user_idintegerUnique ID for a customer.
vendor_idintegerUnique ID for a vendor.
transaction_timetimestampTime when the transaction was recorded.
transaction_dollarsnumericDollar amount of the transaction.
transaction_typetextTransaction type, such as PURCHASE or REFUND.
refund_transaction_idintegerFor a REFUND, the transaction_id of the original PURCHASE.

vendors

columntypedescription
vendor_idintegerUnique ID for a vendor.
citytextCity where the vendor is located.
state_provincetextState or province where the vendor is located.
countrytextTwo-letter country code.

Assume that only PURCHASE rows count as charged dollars.

Write SQL queries for the following tasks:

  1. Find the top 3 vendors by total purchase dollars charged in the last 2 years. Return vendor_id and total_dollars_charged, sorted from highest to lowest total.
  2. For each (state_province, country) location, find the top 3 vendors by total purchase dollars charged in the last 2 years. Return state_province, country, vendor_id, total_dollars, and vendor_rank. Use dense ranking so ties receive the same rank.