Views from SA
codisart opened this issue · 2 comments
codisart commented
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 commented
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 commented
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;