Meta 面试真题解析：SQL 分组统计、邀请关系与作者比例分析

This set of SQL interview questions uses a bookstore database with the following tables: books, authors, transactions, and customers.

books

• book_id INT (KEY)
• title VARCHAR
• author_id INT
• publication_date DATE
• category VARCHAR
• price DOUBLE

authors

• author_id INT (KEY)
• first_name VARCHAR
• last_name VARCHAR
• birthday DATE
• website_url VARCHAR

transactions

• transaction_id INT (KEY)
• book_id INT
• customer_id INT
• payment_amount DOUBLE
• book_count INT
• tax_rate DOUBLE
• discount_rate DOUBLE
• transaction_date DATE
• payment_type VARCHAR

customers

• customer_id INT (KEY)
• first_name VARCHAR
• last_name VARCHAR
• registration_date DATE
• interested_in_categories VARCHAR
• is_rewards_member BOOLEAN
• invited_by_customer_id INT

Questions

• What was the total value of sales and the number of unique paying customers, grouped and sorted in descending order by payment type?
• Existing customers can invite other people to sign up to the bookstore. Find the IDs of the top 5 customers, ordered by the average payment per book made by the people they invited.
• Find the total number of authors. What percentage of them have a website URL that contains .com, and what percentage never made a sale?

这道 Meta SQL 题主要考察聚合统计、分组排序、左连接以及邀请关系链的处理。第一问按 <code>payment_type</code> 分组，统计销售总额和去重后的付费用户数，通常需要对 <code>transactions</code> 做 <code>GROUP BY</code> 并使用 <code>COUNT(DISTINCT customer_id)</code>。第二问要沿着 <code>customers.invited_by_customer_id</code> 追踪被邀请用户，再基于这些用户的交易记录计算“每本书的平均支付金额”，最后对邀请者排序取前 5。第三问则是作者维度的比例分析，需要把 <code>authors</code> 与交易表关联，计算总作者数、网站包含 <code>.com</code> 的作者占比，以及从未产生过销售的作者占比。整体思路以多表连接、条件聚合和去重统计为核心。