使用多个 WITH 子查询的替代方法

问：

我正在通过 Google 数据分析专业证书的 Cyclistic 案例研究来练习数据分析。在我的分析过程中，我开发了这个查询来获取按季节共享的自行车数据。

什么是不那么口头/更聪明的方法，因为从子查询到子查询唯一变化的是季节名称和月份间隔？

WITH summer AS (
    SELECT
        'summer' AS season,
        avg(ride_duration) AS avg_ride_duration,
        max(ride_duration) AS max_ride_duration,
        count(ride_id) AS ride_count,
        mode() WITHIN GROUP (ORDER BY day_of_week) AS mode_day_of_week
    FROM trip_data_total
    WHERE
        extract(month FROM started_at) BETWEEN 06 AND 08
),
autumn AS (
    SELECT
        'autumn' AS season,
        avg(ride_duration) AS avg_ride_duration,
        max(ride_duration) AS max_ride_duration,
        count(ride_id) AS ride_count,
        mode() WITHIN GROUP (ORDER BY day_of_week) AS mode_day_of_week
    FROM trip_data_total
    WHERE
        extract(month FROM started_at) BETWEEN 09 AND 11
),
winter AS (
    SELECT
        'winter' AS season,
        avg(ride_duration) AS avg_ride_duration,
        max(ride_duration) AS max_ride_duration,
        count(ride_id) AS ride_count,
        mode() WITHIN GROUP (ORDER BY day_of_week) AS mode_day_of_week
    FROM trip_data_total
    WHERE
        extract(month FROM started_at) IN (12,01,02)
),
spring AS (
    SELECT
        'spring' AS season,
        avg(ride_duration) AS avg_ride_duration,
        max(ride_duration) AS max_ride_duration,
        count(ride_id) AS ride_count,
        mode() WITHIN GROUP (ORDER BY day_of_week) AS mode_day_of_week
    FROM trip_data_total
    WHERE
        extract(month FROM started_at) BETWEEN 03 AND 05
)
SELECT * FROM summer
UNION ALL
SELECT * FROM autumn
UNION ALL
SELECT * FROM winter
UNION ALL
SELECT * FROM spring
ORDER BY ride_count DESC