sql面试题

google product analyst销售表customer | timestamp | sales
求每年年初与年末销售增长最大的前10名用户

1. select *,
   
2.        rank() over (order by diff desc)
   
3. from (select *,
   
4.              sm - sm1 as diff
   
5.       from (select customer,
   
6.                    sum(case when month = 1 then volume end) as sm1,
   
7.                    sum(case when month = 12 then volume end) as sm2
   
8.             from tableA
   
9.             group by customer
   
10.             order by customer) sub1) sub2
    
11. where diff > 0
    
12. limit 10

复制代码

请问题主，这个题目是问的每年的，还是只有一年的数据？

Sudo code for reference, need to modify date functions based on SQL language. PARTITION BY year and using DENSE_RANK to filter out results:
SELECT ORDER_YEAR, CUSTOMER_ID, VOLUMN_VALUE
FROM (
        SELECT
                ORDER_TS(YEAR) as ORDER_YEAR
                ,CUSTOMER_ID
                ,VOLUMN_END-VOLUMN_START AS VOLUMN_VALUE
                ,SUM(CASE WHEN ORDER_TS(MONTH) = 01 THEN VOLUME END) AS VOLUMN_START
                ,SUM(CASE WHEN ORDER_TS(MONTH) = 12 THEN VOLUME END) AS VOLUMN_END
                ,DENSE_RANK() OVER (PARTITION BY ORDER_TS(YEAR) ORDER BY VOLUMN_VALUE DESC) AS VOLUMN_RANK
        FROM tableA
        GROUP BY 1,2,3) a
WHERE VOLUMN_RANK < 11
ORDER BY 1 DESC, 3 DESC, 2;