Skip to content

Instantly share code, notes, and snippets.

@athanhat
Created November 3, 2022 09:58
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save athanhat/9f311959d462339419ab030fee87fa7d to your computer and use it in GitHub Desktop.
Race Stats Summary
```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"
```
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment