Google Data Studio Stories

google-data-studio.sql

SELECT "stories"."id"                     AS "Id",
       "stories"."title"                  AS "Title",
       "stories"."publication_id"         AS "Publication Id",
       "publications"."name"              AS "Publication Name",
       "story_authors"."authors"          AS "Authors",
       "stories"."date"                   AS "Date",
       "stories"."media_type"             AS "Media Type",
       "stories"."url"                    AS "URL",
       "stories"."length"                 AS "Length",
       "stories"."solutions_3"            AS "Solutions 3",
       "stories"."catalyzed_by_sjn"       AS "Catalyzed by SJN",
       "stories"."latitude"               AS "Latitude",
       "stories"."longitude"              AS "Longitude",
       "stories"."city"                   AS "City",
       "stories"."state"                  AS "State",
       "stories"."country"                AS "Country",
       "stories"."story_scope"            AS "Story Scope",
       "stories"."research"               AS "Research",
       "stories"."hashed_id"              AS "Hashed Id",
       "stories"."favorite_count"         AS "Favorite Count",
       "stories"."share_count"            AS "Share Count",
       "stories"."wow_high_cal"           AS "WOW (High Caliber)",
       "stories"."wow_impact"             AS "WOW (Impactful)",
       "stories"."wow_storytelling"       AS "WOW (Innovative Storytelling)",
       "users"."email"                    AS "Created by User",
       "stories"."description"            AS "Description Id",
       "stories"."audio_description"      AS "Audio Description",
       "critical_success_factors"."terms" AS "Success Factors",
       "issue_areas"."terms"              AS "Issue Areas"
FROM   "stories"
       LEFT JOIN "users"
              ON ( "stories"."admin_user_id" = "users"."id" )
       LEFT JOIN "publications"
              ON ( "stories"."publication_id" = "publications"."id" )
       LEFT JOIN (SELECT "story_authors"."story_id",
                         array_to_string(array_agg("authors"."name"), ' | ') AS "authors"
                  FROM   "story_authors"
                         INNER JOIN "authors"
                                 ON ( "story_authors"."author_id" = "authors"."id" )
                  GROUP BY "story_authors"."story_id") AS "story_authors"
              ON ( "stories"."id" = "story_authors"."story_id" )
       LEFT JOIN (SELECT "story_critical_success_factors"."story_id",
                         array_to_string(array_agg("critical_success_factors"."term"), ', ') AS "terms"
                  FROM   "critical_success_factors"
                         INNER JOIN "story_critical_success_factors"
                                 ON ( "story_critical_success_factors"."critical_success_factor_id" = "critical_success_factors"."id" )
                  GROUP BY "story_critical_success_factors"."story_id") AS "critical_success_factors"
              ON ( "critical_success_factors"."story_id" = "stories"."id" )
       LEFT JOIN (SELECT "story_issue_areas"."story_id",
                         array_to_string(array_agg("issue_areas"."term"), ', ') AS "terms"
                  FROM   "issue_areas"
                         INNER JOIN "story_issue_areas"
                                 ON ( "story_issue_areas"."issue_area_id" = "issue_areas"."id" )
                  GROUP BY "story_issue_areas"."story_id") AS "issue_areas"
              ON ( "issue_areas"."story_id" = "stories"."id" )
ORDER BY lower("stories"."title") ASC;