题解 | SQLW15 请写出计算粉丝ctr的sql语句

WITH t1 AS (
    SELECT c.content_id,
           c.fans_id,
           c.read_num,
           c.show_num,
           b.author_id
    FROM c
    LEFT JOIN b
    ON c.content_id = b.content_id
),
t2 AS (
    SELECT t1.author_id,
           t1.fans_id,
           t1.content_id,
           t1.read_num,
           t1.show_num,
           a.create_date
    FROM t1
    LEFT JOIN a
    ON (t1.author_id,t1.fans_id) = (a.author_id,a.fans_id)
    WHERE create_date IS NOT NULL
)
SELECT
       ROUND(SUM(read_num) / SUM(show_num),4)
           AS fans_ctr
FROM t2;

# 这道题的关键在于三表链接的时候条件，以及show_num字段的使用情况！