Changes between Version 2 and Version 3 of AdvancedSqlAndStoredProcedures

Timestamp:

08/24/25 16:42:43 (9 days ago)

Author:

221007

Comment:

--

AdvancedSqlAndStoredProcedures

@@ -182 +182 @@
 {{{
 WITH
-            selected_person_reports AS (
-                SELECT * FROM report WHERE person_id = :person_id
-            ),
-            
-            academic AS (
-                SELECT r.created_at, ar.academic_field, ar.report_id
-                FROM report r
-                         JOIN academicreport ar ON r.report_id = ar.report_id
-                         JOIN institution i ON i.institution_id = ar.institution_id
-                WHERE r.person_id = :person_id
-            ),
-            
-            employment AS (
-                SELECT e.start_date, COALESCE(e.end_date, CURRENT_DATE) AS end_date, e.income_per_month
-                FROM employmentreport e
-                         JOIN report r ON r.report_id = e.report_id
-                WHERE r.person_id = :person_id
-            ),
-            
-            medical AS (
-                SELECT d.short_description, d.is_chronic
-                FROM report r
-                         JOIN medicalreport mr ON r.report_id = mr.report_id
-                         JOIN medicalreport_diagnosis mrd ON mrd.report_id = mr.report_id
-                         JOIN diagnosis d ON d.diagnosis_id = mrd.diagnosis_id
-                WHERE r.person_id = :person_id
-            ),
-            
-            criminal AS (
-                SELECT cr.descriptive_punishment, cr.resolved as is_resolved
-                FROM report r
-                         JOIN criminalreport cr ON r.report_id = cr.report_id
-                         JOIN crimetype ct ON ct.crime_type_id = cr.crime_type_id
-                WHERE r.person_id = :person_id
-            ),
-            
-            ordered_academic_reports_by_date AS (
-                SELECT
-                    ar.academic_field,
-                    r.created_at,
-                    ROW_NUMBER() OVER (PARTITION BY ar.academic_field ORDER BY r.created_at) AS row_num
-                FROM report r
-                         JOIN academicreport ar ON ar.report_id = r.report_id
-                WHERE r.person_id = :person_id
-            ),
-            
-            filtered_academic_pathway AS (
-                SELECT academic_field, MIN(created_at) AS started_on
-                FROM ordered_academic_reports_by_date
-                GROUP BY academic_field
-            ),
-            
-            employment_report_stats AS (
-                SELECT
-                    CAST(SUM(ABS(COALESCE(e.end_date, CURRENT_DATE) - e.start_date)) AS BIGINT) AS total_working_in_days,
-                    CAST(CEIL(SUM(ABS(COALESCE(e.end_date, CURRENT_DATE) - e.start_date)) / 30.0) AS BIGINT) AS total_working_in_months,
-                    CAST(CEIL(SUM(ABS(COALESCE(e.end_date, CURRENT_DATE) - e.start_date)) / 365.0) AS BIGINT) AS total_working_in_years,
-                    CAST(MAX(COALESCE(e.end_date, CURRENT_DATE) - e.start_date) AS BIGINT) AS longest_job_days
-                FROM employmentreport e
-                         JOIN report r ON r.report_id = e.report_id
-                WHERE r.person_id = :person_id
-            )
-            SELECT
-            -- General
-            CAST((SELECT COUNT(*) FROM selected_person_reports) AS BIGINT) AS total_reports_found,
-            CAST((SELECT created_at::date FROM selected_person_reports ORDER BY created_at ASC LIMIT 1) AS DATE) AS first_report_of_person,
-            CAST((SELECT created_at::date FROM selected_person_reports ORDER BY created_at DESC LIMIT 1) AS DATE) AS latest_report_of_person,
-            -- Academic
-            CAST((SELECT COUNT(*) FROM academic) AS BIGINT) AS academic_total,
-            CAST((SELECT academic_field
-                  FROM academic
-                  GROUP BY academic_field
-                  ORDER BY COUNT(*) DESC
-                LIMIT 1) AS TEXT) AS most_common_field,
-            CAST((SELECT STRING_AGG(academic_field, ' → ' ORDER BY started_on)
-                  FROM filtered_academic_pathway) AS TEXT) AS education_path,
-            -- Employment
-            CAST((SELECT COUNT(*) FROM employment) AS BIGINT) AS job_count,
-            (SELECT total_working_in_days FROM employment_report_stats),
-            (SELECT total_working_in_months FROM employment_report_stats),
-            (SELECT total_working_in_years FROM employment_report_stats),
-            (SELECT longest_job_days FROM employment_report_stats),
-            CAST((SELECT MAX(income_per_month) FROM employment) AS DOUBLE PRECISION) AS max_income_from_job,
-            -- Medical
-            CAST((SELECT COUNT(*) FROM medical) AS BIGINT) AS diagnosis_total,
-            CAST((SELECT ROUND(SUM(CASE WHEN is_chronic THEN 1 ELSE 0 END)::decimal / COUNT(*), 2)
-                  FROM medical) AS DOUBLE PRECISION) AS chronic_ratio,
-            CAST((SELECT short_description
-                  FROM medical
-                  GROUP BY short_description
-                  ORDER BY COUNT(*) DESC
-                LIMIT 1) AS TEXT) AS most_frequent_diagnosis,
-            -- Criminal
-            CAST((SELECT COUNT(*) FROM criminal) AS BIGINT) AS criminal_case_total,
-            CAST((SELECT ROUND(SUM(CASE WHEN is_resolved THEN 1 ELSE 0 END)::decimal / COUNT(*), 2)
-                  FROM criminal) AS DOUBLE PRECISION) AS resolution_ratio
-}}}
+    selected_person_reports AS (
+        SELECT * FROM report WHERE person_id = :person_id
+    ),
+
+    academic AS (
+        SELECT r.created_at, ar.academic_field, ar.report_id
+        FROM report r
+                 JOIN academicreport ar ON r.report_id = ar.report_id
+                 JOIN institution i ON i.institution_id = ar.institution_id
+        WHERE r.person_id = :person_id
+    ),
+
+    employment AS (
+        SELECT e.start_date, COALESCE(e.end_date, CURRENT_DATE) AS end_date, e.income_per_month
+        FROM employmentreport e
+                 JOIN report r ON r.report_id = e.report_id
+        WHERE r.person_id = :person_id
+    ),
+
+    medical AS (
+        SELECT d.short_description, d.is_chronic
+        FROM report r
+                 JOIN medicalreport mr ON r.report_id = mr.report_id
+                 JOIN medicalreport_diagnosis mrd ON mrd.report_id = mr.report_id
+                 JOIN diagnosis d ON d.diagnosis_id = mrd.diagnosis_id
+        WHERE r.person_id = :person_id
+    ),
+
+    criminal AS (
+        SELECT cr.descriptive_punishment, cr.resolved as is_resolved
+        FROM report r
+                 JOIN criminalreport cr ON r.report_id = cr.report_id
+                 JOIN crimetype ct ON ct.crime_type_id = cr.crime_type_id
+        WHERE r.person_id = :person_id
+    ),
+
+    ordered_academic_reports_by_date AS (
+        SELECT
+            ar.academic_field,
+            r.created_at,
+            ROW_NUMBER() OVER (PARTITION BY ar.academic_field ORDER BY r.created_at) AS row_num
+        FROM report r
+                 JOIN academicreport ar ON ar.report_id = r.report_id
+        WHERE r.person_id = :person_id
+    ),
+
+    filtered_academic_pathway AS (
+        SELECT academic_field, MIN(created_at) AS started_on
+        FROM ordered_academic_reports_by_date
+        GROUP BY academic_field
+    ),
+
+    employment_report_stats AS (
+        SELECT
+            CAST(SUM(ABS(COALESCE(e.end_date, CURRENT_DATE) - e.start_date)) AS BIGINT) AS total_working_in_days,
+            CAST(CEIL(SUM(ABS(COALESCE(e.end_date, CURRENT_DATE) - e.start_date)) / 30.0) AS BIGINT) AS total_working_in_months,
+            CAST(CEIL(SUM(ABS(COALESCE(e.end_date, CURRENT_DATE) - e.start_date)) / 365.0) AS BIGINT) AS total_working_in_years,
+            CAST(MAX(COALESCE(e.end_date, CURRENT_DATE) - e.start_date) AS BIGINT) AS longest_job_days
+        FROM employmentreport e
+                 JOIN report r ON r.report_id = e.report_id
+        WHERE r.person_id = :person_id
+    )
+SELECT
+    -- General
+    CAST((SELECT COUNT(*) FROM selected_person_reports) AS BIGINT) AS total_reports_found,
+    CAST((SELECT created_at::date FROM selected_person_reports ORDER BY created_at ASC LIMIT 1) AS DATE) AS first_report_of_person,
+    CAST((SELECT created_at::date FROM selected_person_reports ORDER BY created_at DESC LIMIT 1) AS DATE) AS latest_report_of_person,
+    -- Academic
+    CAST((SELECT COUNT(*) FROM academic) AS BIGINT) AS academic_total,
+    CAST((SELECT academic_field
+          FROM academic
+          GROUP BY academic_field
+          ORDER BY COUNT(*) DESC
+        LIMIT 1) AS TEXT) AS most_common_field,
+    CAST((SELECT STRING_AGG(academic_field, ' → ' ORDER BY started_on)
+          FROM filtered_academic_pathway) AS TEXT) AS education_path,
+    -- Employment
+    CAST((SELECT COUNT(*) FROM employment) AS BIGINT) AS job_count,
+    (SELECT total_working_in_days FROM employment_report_stats),
+    (SELECT total_working_in_months FROM employment_report_stats),
+    (SELECT total_working_in_years FROM employment_report_stats),
+    (SELECT longest_job_days FROM employment_report_stats),
+    CAST((SELECT MAX(income_per_month) FROM employment) AS DOUBLE PRECISION) AS max_income_from_job,
+    -- Medical
+    CAST((SELECT COUNT(*) FROM medical) AS BIGINT) AS diagnosis_total,
+    CAST((SELECT ROUND(SUM(CASE WHEN is_chronic THEN 1 ELSE 0 END)::decimal / COUNT(*), 2)
+          FROM medical) AS DOUBLE PRECISION) AS chronic_ratio,
+    CAST((SELECT short_description
+          FROM medical
+          GROUP BY short_description
+          ORDER BY COUNT(*) DESC
+        LIMIT 1) AS TEXT) AS most_frequent_diagnosis,
+    -- Criminal
+    CAST((SELECT COUNT(*) FROM criminal) AS BIGINT) AS criminal_case_total,
+    CAST((SELECT ROUND(SUM(CASE WHEN is_resolved THEN 1 ELSE 0 END)::decimal / COUNT(*), 2)
+          FROM criminal) AS DOUBLE PRECISION) AS resolution_ratio;
+}}}