WITH RECURSIVE DateRange AS (
    SELECT :startDate AS date_value
    UNION ALL
    SELECT DATE_ADD(date_value, INTERVAL 1 DAY)
    FROM DateRange
    WHERE DATE_ADD(date_value, INTERVAL 1 DAY) <= :endDate
),
dialogueData AS (
    SELECT 
        DATE(datetime) AS dialogue_date, 
        COUNT(DISTINCT member_id) AS member_count,
        COUNT(relation_id) AS usage_count
    FROM 
        biz_data_analyze_dialogue_record
    WHERE 1=1
        << and relation_id = :relationId >>
        << and datetime >= :startDate and datetime <=  :endDate >>
        << and channel in (:channels) >>
    GROUP BY 
        DATE(datetime)
),
pointData AS (
    SELECT 
        DATE(datetime) AS consume_date, 
        SUM(point_num) AS total_points
    FROM 
        biz_data_analyze_point_record
    WHERE 1=1
        << and relation_id = :relationId >>
        << and datetime >= :startDate and datetime <=  :endDate >>
        << and channel in (:channels) >>
    GROUP BY 
        DATE(datetime)
)
SELECT 
    dr.date_value AS dialogue_date,
    COALESCE(dd.member_count, 0) AS member_count,
    COALESCE(dd.usage_count, 0) AS usage_count,
    COALESCE(pd.total_points, 0) AS total_points
FROM
    DateRange dr
LEFT JOIN
    dialogueData dd ON dr.date_value = dd.dialogue_date
LEFT JOIN
    pointData pd ON dr.date_value = pd.consume_date
ORDER BY 
    dr.date_value