Views from SA

[codisart]

main_summaries

      WITH incidents_users AS (
           SELECT a.user_id,
              incidents.id,
              ((incidents.rendered_obsolete_at IS NULL) AND ((incidents.ends_at IS NULL) OR (incidents.ends_at > now()))) AS is_valid,
              (incidents.seen_at IS NULL) AS is_new,
              (incidents.dismissed_at IS NULL) AS is_pending
             FROM (incidents
               JOIN alerts a ON ((a.id = incidents.alert_id)))
          ), total_new_incidents AS (
           SELECT incidents_users.user_id,
              'new_incidents'::text AS label,
              incidents_users.id
             FROM incidents_users
            WHERE (incidents_users.is_new AND incidents_users.is_valid)
          ), total_pending_incidents AS (
           SELECT incidents_users.user_id,
              'pending_incidents'::text AS label,
              incidents_users.id
             FROM incidents_users
            WHERE ((NOT incidents_users.is_new) AND incidents_users.is_pending AND incidents_users.is_valid)
          ), total_non_deleted_alerts AS (
           SELECT alerts.user_id,
              'alerts'::text AS label,
              alerts.id
             FROM alerts
            WHERE ((alerts.deleted_at IS NULL) AND (alerts.archived_at IS NULL))
          ), total_satellites AS (
           SELECT satellites.user_id,
              'satellites'::text AS label,
              satellites.id
             FROM satellites
          ), total_space_objects AS (
           SELECT count(space_objects.id) AS total
             FROM space_objects
          )
   SELECT users.id AS user_id,
      count(users_relations.id) FILTER (WHERE (users_relations.label = 'new_incidents'::text)) AS new_incidents,
      count(users_relations.id) FILTER (WHERE (users_relations.label = 'pending_incidents'::text)) AS pending_incidents,
      count(users_relations.id) FILTER (WHERE (users_relations.label = 'alerts'::text)) AS trackers,
      count(users_relations.id) FILTER (WHERE (users_relations.label = 'satellites'::text)) AS simulated_satellites,
      total_space_objects.total AS ingested_objects
     FROM ((users
       LEFT JOIN ( SELECT total_new_incidents.user_id,
              total_new_incidents.label,
              total_new_incidents.id
             FROM total_new_incidents
          UNION ALL
           SELECT total_pending_incidents.user_id,
              total_pending_incidents.label,
              total_pending_incidents.id
             FROM total_pending_incidents
          UNION ALL
           SELECT total_non_deleted_alerts.user_id,
              total_non_deleted_alerts.label,
              total_non_deleted_alerts.id
             FROM total_non_deleted_alerts
          UNION ALL
           SELECT total_satellites.user_id,
              total_satellites.label,
              total_satellites.id
             FROM total_satellites) users_relations ON ((users_relations.user_id = users.id)))
       CROSS JOIN total_space_objects)
    GROUP BY users.id, total_space_objects.total;

[codisart]

global_search_results

WITH searchable_simulated_spacecrafts AS (
           SELECT satellites.user_id,
              'simulated_spacecraft'::text AS category,
              satellites.name AS searchable,
              satellites.updated_at,
              json_build_object('id', satellites.id, 'name', satellites.name) AS details
             FROM satellites
          ), searchable_space_objects AS (
           SELECT NULL::integer AS user_id,
              'space_object'::text AS category,
              space_objects.name AS searchable,
              space_objects.updated_at,
              json_build_object('id', space_objects.id, 'name', space_objects.name, 'catalog_number', space_objects.catalog_number, 'international_designator', space_objects.international_designator) AS details
             FROM space_objects
          ), trackers_sub_query AS (
           SELECT alerts.user_id,
              alerts.name,
              alerts.updated_at,
              alerts.id,
              alerts.object_id AS space_object_id,
              space_objects.name AS space_object_name
             FROM (alerts
               JOIN space_objects ON ((space_objects.id = alerts.object_id)))
            WHERE (((alerts.object_type)::text = 'SpaceObject'::text) AND (alerts.deleted_at IS NULL) AND (alerts.archived_at IS NULL))
          ), searchable_trackers AS (
           SELECT trackers_sub_query.user_id,
              'tracker'::text AS category,
              trackers_sub_query.name AS searchable,
              trackers_sub_query.updated_at,
              json_build_object('id', trackers_sub_query.id, 'name', trackers_sub_query.name, 'space_object_id', trackers_sub_query.space_object_id, 'space_object_name', trackers_sub_query.space_object_name) AS details
             FROM trackers_sub_query
          ), searchable_notifications AS (
           SELECT trackers.user_id,
              'notification'::text AS category,
              trackers.space_object_name AS searchable,
              incidents.updated_at,
              json_build_object('id', incidents.id, 'starts_at', to_char(incidents.starts_at, 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"'::text), 'tracker_name', trackers.name, 'tracker_id', incidents.alert_id, 'space_object_id', trackers.space_object_id, 'space_object_name', trackers.space_object_name) AS json_build_object
             FROM (incidents
               JOIN trackers_sub_query trackers ON (((incidents.alert_id = trackers.id) AND (incidents.dismissed_at IS NULL) AND (incidents.rendered_obsolete_at IS NULL) AND ((incidents.ends_at IS NULL) OR (incidents.ends_at > now())))))
          )
   SELECT searchable_simulated_spacecrafts.user_id,
      searchable_simulated_spacecrafts.category,
      searchable_simulated_spacecrafts.searchable,
      searchable_simulated_spacecrafts.updated_at,
      searchable_simulated_spacecrafts.details
     FROM searchable_simulated_spacecrafts
  UNION ALL
   SELECT searchable_space_objects.user_id,
      searchable_space_objects.category,
      searchable_space_objects.searchable,
      searchable_space_objects.updated_at,
      searchable_space_objects.details
     FROM searchable_space_objects
  UNION ALL
   SELECT searchable_trackers.user_id,
      searchable_trackers.category,
      searchable_trackers.searchable,
      searchable_trackers.updated_at,
      searchable_trackers.details
     FROM searchable_trackers
  UNION ALL
   SELECT searchable_notifications.user_id,
      searchable_notifications.category,
      searchable_notifications.searchable,
      searchable_notifications.updated_at,
      searchable_notifications.json_build_object AS details
     FROM searchable_notifications;

[codisart]

product_analytics

WITH scopes AS (
           SELECT 'monthly'::text AS label,
              ((now() - 'P30D'::interval))::date AS date
          UNION
           SELECT 'quarterly'::text AS label,
              ((now() - 'P90D'::interval))::date AS date
          UNION
           SELECT 'yearly'::text AS label,
              ((now() - 'P1Y'::interval))::date AS date
          ), deleted_alerts AS (
           SELECT alert_versions.item_id AS alert_id,
              alert_versions.created_at AS changed_at,
              (((alert_versions.object_changes -> 'archived_at'::text) ->> 0) IS NULL) AS was_deleted
             FROM alert_versions
            WHERE ((alert_versions.object_changes ->> 'archived_at'::text) IS NOT NULL)
          ), edited_alerts AS (
           SELECT alert_versions.item_id AS alert_id,
              alert_versions.created_at AS edited_at
             FROM alert_versions
            WHERE (((alert_versions.event)::text = 'update'::text) AND (((alert_versions.object_changes ->> 'name'::text) IS NOT NULL) OR ((alert_versions.object_changes ->> 'risk_threshold'::text) IS NOT NULL) OR ((alert_versions.object_changes ->> 'expires_at'::text) IS NOT NULL) OR ((alert_versions.object_changes ->> 'notification_type'::text) IS NOT NULL)))
          )
   SELECT users.id AS user_id,
      scopes.label AS period,
      count(DISTINCT incidents.id) FILTER (WHERE (incidents.created_at > scopes.date)) AS total_received_incidents,
      count(DISTINCT incidents.id) FILTER (WHERE ((incidents.dismissed_at IS NOT NULL) AND (incidents.dismissed_at > scopes.date))) AS total_closed_incidents,
      count(DISTINCT incidents.id) FILTER (WHERE (incidents.seen_at IS NULL)) AS total_pending_incidents,
      count(DISTINCT alerts.id) FILTER (WHERE (alerts.created_at > scopes.date)) AS total_created_alerts,
      count(DISTINCT deleted_alerts.alert_id) FILTER (WHERE deleted_alerts.was_deleted) AS total_deleted_alerts,
      count(DISTINCT deleted_alerts.alert_id) FILTER (WHERE (NOT deleted_alerts.was_deleted)) AS total_restored_alerts,
      count(DISTINCT edited_alerts.alert_id) AS total_edited_alerts
     FROM (((((users
       CROSS JOIN scopes)
       LEFT JOIN alerts ON ((users.id = alerts.user_id)))
       LEFT JOIN incidents ON (((alerts.id = incidents.alert_id) AND ((incidents.ends_at IS NULL) OR (incidents.ends_at > scopes.date)))))
       LEFT JOIN deleted_alerts ON (((alerts.id = deleted_alerts.alert_id) AND (deleted_alerts.changed_at > scopes.date))))
       LEFT JOIN edited_alerts ON (((alerts.id = edited_alerts.alert_id) AND (edited_alerts.edited_at > scopes.date))))
    GROUP BY users.id, scopes.label;