hive - 面试题 - 最近一次购物在一年前（近一年内无购物）

要求：
有表：用户id，订单id，下单日期
该用户符合365天内无交易且当日有交易的数据打标签，如果当天有多条记录，同样打标签。

思路：
当前订单时间 - 最近一次的下单时间 >= 365 ，即最近365天内无订单记录。
中间有个问题，一天内多次下单，只能先写子查询去重，后续再关联出全天所有记录，不知道有没有其他合适的方式。

WITH o AS 
(
    SELECT  '01' AS userid
            ,'1001' AS orderid
            ,CAST('2023-01-01' AS DATE) AS paydate
    UNION ALL
    SELECT  '01' AS userid
            ,'1002' AS orderid
            ,CAST('2024-08-02' AS DATE) AS paydate
    UNION ALL
    SELECT  '01' AS userid
            ,'1003' AS orderid
            ,CAST('2024-08-02' AS DATE) AS paydate
    UNION ALL
    SELECT  '02' AS userid
            ,'1000' AS orderid
            ,CAST('2021-01-01' AS DATE) AS paydate
    UNION ALL
    SELECT  '02' AS userid
            ,'1004' AS orderid
            ,CAST('2023-01-02' AS DATE) AS paydate
    UNION ALL
    SELECT  '02' AS userid
            ,'1005' AS orderid
            ,CAST('2023-08-02' AS DATE) AS paydate
    UNION ALL
    SELECT  '03' AS userid
            ,'1006' AS orderid
            ,CAST('2023-01-01' AS DATE) AS paydate
)
SELECT  o.*
        ,d.flag
FROM    o AS o
LEFT JOIN   (
                SELECT  userid
                        ,paydate
                        ,f_date
                        ,CASE   WHEN DATEDIFF(paydate,f_date) >= 365 THEN 'yes'
                                ELSE 'no'
                        END AS flag
                FROM    (
                            SELECT  userid
                                    ,paydate
                                    ,LAG(paydate,1,'') OVER (PARTITION BY userid ORDER BY paydate ASC ) AS f_date
                            FROM    (
                                        SELECT  userid
                                                ,paydate
                                        FROM    o
                                        GROUP BY userid
                                                 ,paydate
                                    ) AS c
                        ) AS a
            ) AS d
ON      o.userid = d.userid
AND     o.paydate = d.paydate
;

结果：