如何查找在注册成为商店会员的前 90 天内进行交易的客户百分比

Question

我正在尝试查找在成为商店福利会员的前 90 天内进行购买的客户百分比。我最初尝试编写两个单独的查询，然后从那里执行计算，但如果可能的话，我希望将其包含在一个查询中(我仍然是SQL的新手(。我想随着时间的推移进行比较，所以我也包括了福利注册日期。

我正在使用 Amazon Redshift。

我尝试编写两个单独的查询，但如果可能的话，我想将它们组合在一起。

/*
* Numerator (counting customers with purchases within 90 days of becoming members)
*/
SELECT 
    c.customer_id, 
    c.member_start
FROM
     (SELECT * FROM before_2012_data
      UNION ALL 
      SELECT  * FROM after_2012_data
     ) t
     LEFT JOIN customer_information c
     ON c.customer_id = t.customer_id
WHERE DATEDIFF(day, c.member_start, t.purchase_data) between 0 and 90 
GROUP BY  member_start, c.customer_id
ORDER BY member_start
/*
* Denominator (counting customers both with and without purchases within 90 days of becoming members)
*/
SELECT 
  c.customer_id, 
  c.member_start
FROM
     (SELECT * FROM before_2012_data
      UNION ALL 
      SELECT  * FROM after_2012_data
     ) t
     LEFT JOIN customer_information c
     ON c.customer_id = t.customer_id
GROUP BY  member_start, c.customer_id
ORDER BY member_start

Answer 1

希望，我正确理解了你的问题。 您可以查看下面的查询。

SELECT 
    count(case when DATEDIFF(day, c.member_start, t.purchase_data) between 0 and 90  then t.customer_id end) / NULLIF(count(t.customer_id),0)
FROM
     (SELECT * FROM before_2012_data
      UNION ALL 
      SELECT  * FROM after_2012_data
     ) t
     LEFT JOIN customer_information c
     ON c.customer_id = t.customer_id;

Answer 2

您可以将条件聚合与单个查询一起使用：

SELECT
    c.customer_id,
    c.member_start,
    100.0 * COUNT(CASE WHEN DATEDIFF(day, c.member_start, t.purchase_data)
                            BETWEEN 0 AND 90 THEN 1 END) / COUNT(*) AS percentage
FROM
(
    SELECT * FROM before_2012_data
    UNION ALL
    SELECT * FROM after_2012_data
) t
LEFT JOIN customer_information c
    ON c.customer_id = t.customer_id
GROUP BY
    c.customer_id,
    c.member_start
ORDER BY
    c.member_start;