在数据科学领域，掌握SQL查询是至关重要的。本文将讨论产品公司在招聘数据科学家时使用的两个SQL查询实例。这些问题由StrataScratch网站生成，该网站是任何希望开始数据科学之旅并提高SQL和Python技能的人的极佳工具。这个平台提供了与数据科学相关的编码问题和非编码主题，如统计学、概率论等。强烈建议在StrataScratch网站上创建账户，并结合本文一起练习这些问题。为了解决这些问题，将使用Postgres SQL数据库。
如果精通SQL，将更有可能通过数据科学面试或高效处理日常工作任务。本文将专注于解决问题的方法。阅读完本文后，将更好地理解如何针对给定问题提出解决方案。必须阅读本文以提高理解和解决问题的方法。让继续看问题。
找出按日期计算的付费和非付费用户的总下载量。只包括非付费客户下载量超过付费客户的记录。输出应按最早日期排序，并包含3列：日期、非付费下载量和付费下载量。
面试问题日期：2020年11月，公司：微软，难度级别：中等，面试问题ID：10300。涉及的表格有ms_user_dimension（字段：user_id(int), acc_id(int)），ms_acc_dimension（字段：acc_id(int), paying_customer(varchar)），ms_download_facts（字段：date(datetime), user_id(int), downloads(int)）。
解决这个问题，需要确定付费和非付费客户每天的下载量。问题可以分为三个部分。将在第一部分中连接所有表格。第二部分将确定每个用户的付费和非付费下载量。最后，将展示非付费下载量超过付费下载量的记录。
首先，需要结合不同表格的记录。由于user_id在ms_user_dimension和ms_download_facts之间存在，acc_id在ms_user_dimension和ms_acc_dimension之间存在，因此可以使用内连接来连接所有给定的表格，并选择需要的日期、下载量和付费客户列来形成输出。以下是步骤1的代码。
SELECT date, downloads, paying_customer
FROM ms_user_dimension
INNER JOIN ms_acc_dimension ON ms_user_dimension.acc_id = ms_acc_dimension.acc_id
INNER JOIN ms_download_facts ON ms_user_dimension.user_id = ms_download_facts.user_id;
在合并所有记录后，需要显示三列：日期、总付费下载量和总非付费下载量。为此，将使用日期分组，因为想要计算每个日期的下载量总和。此外，需要使用CASE子句来分别显示付费客户和非付费客户的下载量。SQL数据库中的CASE子句类似于if-else条件。在这里，想要计算付费用户和非付费用户的下载量总和，因此使用了CASE子句来区分付费和非付费用户所做的下载。以下是步骤2的代码。
SELECT date,
SUM(CASE WHEN paying_customer = 'yes' THEN downloads END) AS paid_downloads,
SUM(CASE WHEN paying_customer = 'no' THEN downloads END) AS non_paid_downloads
FROM ms_user_dimension
INNER JOIN ms_acc_dimension ON ms_user_dimension.acc_id = ms_acc_dimension.acc_id
INNER JOIN ms_download_facts ON ms_user_dimension.user_id = ms_download_facts.user_id
GROUP BY date;
必须只显示那些付费下载量超过非付费下载量的记录。由于使用了聚合函数计算paid_downloads和non_paid_downloads，因此不能在where子句中使用它们。可以在having子句中添加一个过滤器，用于计算使用聚合函数的字段。最后，可以根据日期对结果进行排序，以便最早的日期首先出现。以下是查询的最终代码。
SELECT date,
SUM(CASE WHEN paying_customer = 'no' THEN downloads END) AS non_paid_downloads,
SUM(CASE WHEN paying_customer = 'yes' THEN downloads END) AS paid_downloads
FROM ms_user_dimension
INNER JOIN ms_acc_dimension ON ms_user_dimension.acc_id = ms_acc_dimension.acc_id
INNER JOIN ms_download_facts ON ms_user_dimension.user_id = ms_download_facts.user_id
GROUP BY date
HAVING SUM(CASE WHEN paying_customer = 'no' THEN downloads END) >
SUM(CASE WHEN paying_customer = 'yes' THEN downloads END)
ORDER BY date;
解决这个问题，将问题分为三个部分。将在第一部分中合并表格中的记录。第二部分将确定每天消耗的总能量。最后，必须确定消耗最多能量的日期并返回结果。
由于数据分布在三个表格中，必须合并所有三个表格中的所有记录。不能使用union来合并这些三个表格中的记录，因为在fb_eu_energy和fb_na_energy中有重复记录。例如，记录（2020-01-01, 400）可以在fb_eu_energy和fb_na_energy表格中找到。因此，如果使用union合并记录，它将消除重复记录。因此，使用了union all，它也会包含重复记录。
SELECT date, consumption
FROM fb_eu_energy
UNION ALL
SELECT date, consumption FROM fb_asia_energy
UNION ALL
SELECT date, consumption FROM fb_na_energy;
在合并所有记录后，将选择每天消耗的能量总和。可以使用日期分组，并计算这一天的总能耗。
SELECT date, SUM(consumption) AS total_consumption
FROM (
SELECT date, consumption FROM fb_eu_energy
UNION ALL
SELECT date, consumption FROM fb_asia_energy
UNION ALL
SELECT date, consumption FROM fb_na_energy
)E
GROUP BY date;
SELECT date, SUM(consumption) AS total_consumption FROM (
SELECT date, consumption FROM fb_eu_energy
UNION ALL
SELECT date, consumption FROM fb_asia_energy
UNION ALL
SELECT date, consumption FROM fb_na_energy
)E
GROUP BY date
ORDER BY SUM(consumption) DESC LIMIT 1;