我通过谷歌数据分析专业证书的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