Startup SQL Interview Questions: Application Tables and Joins

There are 3 tables in the database.

• Application: stores customer application information.
• AppStatusDesc: lookup table for application status.
• ProductDesc: lookup table for product description.

Below are the table structures. You can also view them on the Database tab on the right panel.

Application

UserID int
ApplicationID varchar
StatusCode int
AppDate date
StatusDate date
Channel varchar
CampaignID varchar
ProductID int

AppStatusDesc

StatusCode int
Description varchar

ProductDesc

ProductCode int
ProductName varchar

Please solve the following questions:

• Q1. How many applications are completed regardless of product?
• Q2. How long do completed applications take in 2024 for each product?
• Q3. Please list all declined credit card applications from DM channel and campaign 2401.
• Q4. For each customer, please list their latest application.

This is a classic SQL interview problem focused on multi-table joins, filtering, grouping, and latest-record selection. You need to combine the Application fact table with the AppStatusDesc and ProductDesc lookup tables, then answer questions about completed applications, processing time in 2024 by product, declined credit card applications from a specific channel and campaign, and each customer's latest application. Typical techniques include joining dimension tables, filtering by status description, calculating date differences, and using window functions or subqueries to pick the most recent row per user.