Startup 面试真题解析：Application 表、状态表与产品表 SQL 题

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.

这道题是典型的 SQL 多表查询与业务筛选题，核心围绕 Application 事实表、AppStatusDesc 状态字典表和 ProductDesc 产品维表展开。题目会考察你能否正确使用 JOIN 还原状态与产品名称，并结合条件过滤、按产品分组统计、日期差计算，以及通过窗口函数或子查询找出每个用户的最新申请记录。尤其要注意 Completed、Declined 这类状态需要先通过状态表映射后再筛选，Q2 还涉及 2024 年完成申请的时长统计，通常需要计算 AppDate 和 StatusDate 的差值。