Race Stats Summary

race_stats_summary.sql

```sql
SELECT
  coalesce(
    json_agg(
      "root"
      ORDER BY
        "root.pg.rss_elapsed_min" DESC NULLS FIRST
    ),
    '[]'
  ) AS "root"
FROM
  (
    SELECT
      "_root.base"."rss_elapsed_min" AS "root.pg.rss_elapsed_min",
      row_to_json(
        (
          SELECT
            "_e"
          FROM
            (
              SELECT
                "_root.base"."rss_pk" AS "rss_pk",
                "_root.base"."rss_type" AS "rss_type",
                "_root.base"."rss_description" AS "rss_description",
                "_root.base"."code_rss_intype_id" AS "code_rss_intype_id",
                "_root.base"."rss_inserted_at" AS "rss_inserted_at",
                "_root.base"."rss_elapsed_min" AS "rss_elapsed_min",
                "_root.base"."rss_runners_started" AS "rss_runners_started",
                "_root.base"."rss_runners_finished" AS "rss_runners_finished",
                "_root.base"."rss_runners_active" AS "rss_runners_active",
                "_root.base"."rss_runners_participated" AS "rss_runners_participated",
                "_root.base"."rss_runners_registered" AS "rss_runners_registered",
                "_root.base"."rss_runners_dropped" AS "rss_runners_dropped",
                "_root.ar.root.race_stats_densities"."race_stats_densities" AS "race_stats_densities"
            ) AS "_e"
        )
      ) AS "root"
    FROM
      (
        SELECT
          *
        FROM
          "public"."bacm2022_race_stats_summary_search"(
            ('full_marathon') :: text,
            ('CURRENT') :: text,
            ('124') :: smallint
          ) AS "_bacm2022_race_stats_summary_search"
        WHERE
          ('true')
        ORDER BY
          "rss_elapsed_min" DESC NULLS FIRST
        LIMIT
          1
      ) AS "_root.base"
      LEFT OUTER JOIN LATERAL (
        SELECT
          coalesce(
            json_agg(
              "race_stats_densities"
              ORDER BY
                "root.ar.root.race_stats_densities.pg.race_segment_id" ASC NULLS LAST
            ),
            '[]'
          ) AS "race_stats_densities"
        FROM
          (
            SELECT
              row_to_json(
                (
                  SELECT
                    "_e"
                  FROM
                    (
                      SELECT
                        "_root.ar.root.race_stats_densities.base"."rd_value" AS "rd_value",
                        "_root.ar.root.race_stats_densities.base"."rd_passed0" AS "rd_passed0",
                        "_root.ar.root.race_stats_densities.base"."rd_passed1" AS "rd_passed1",
                        "_root.ar.root.race_stats_densities.base"."rd_active" AS "rd_active",
                        "_root.ar.root.race_stats_densities.or.race_segment"."race_segment" AS "race_segment"
                    ) AS "_e"
                )
              ) AS "race_stats_densities",
              "_root.ar.root.race_stats_densities.base"."race_segment_id" AS "root.ar.root.race_stats_densities.pg.race_segment_id"
            FROM
              (
                SELECT
                  *
                FROM
                  "bacm2022"."race_stats_density"
                WHERE
                  (
                    ("_root.base"."rss_pk") = ("race_stats_summary_id")
                  )
                ORDER BY
                  "race_segment_id" ASC NULLS LAST
              ) AS "_root.ar.root.race_stats_densities.base"
              LEFT OUTER JOIN LATERAL (
                SELECT
                  row_to_json(
                    (
                      SELECT
                        "_e"
                      FROM
                        (
                          SELECT
                            "_root.ar.root.race_stats_densities.or.race_segment.base"."rs_type" AS "rs_type",
                            "_root.ar.root.race_stats_densities.or.race_segment.base"."rs_subtype" AS "rs_subtype",
                            "_root.ar.root.race_stats_densities.or.race_segment.base"."rs_name" AS "rs_name",
                            "_root.ar.root.race_stats_densities.or.race_segment.base"."rs_code" AS "rs_code",
                            "_root.ar.root.race_stats_densities.or.race_segment.base"."code_state_id" AS "code_state_id"
                        ) AS "_e"
                    )
                  ) AS "race_segment"
                FROM
                  (
                    SELECT
                      *
                    FROM
                      "bacm2022"."race_segment"
                    WHERE
                      (
                        (
                          "_root.ar.root.race_stats_densities.base"."race_segment_id"
                        ) = ("rs_code")
                      )
                    LIMIT
                      1
                  ) AS "_root.ar.root.race_stats_densities.or.race_segment.base"
              ) AS "_root.ar.root.race_stats_densities.or.race_segment" ON ('true')
            ORDER BY
              "root.ar.root.race_stats_densities.pg.race_segment_id" ASC NULLS LAST
          ) AS "_root.ar.root.race_stats_densities"
      ) AS "_root.ar.root.race_stats_densities" ON ('true')
    ORDER BY
      "root.pg.rss_elapsed_min" DESC NULLS FIRST
  ) AS "_root"
```