<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Adrian Mageto</title>
    <description>The latest articles on DEV Community by Adrian Mageto (@adrian_mageto).</description>
    <link>https://dev.to/adrian_mageto</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4070904%2F9ae7b655-6643-43d1-b5bb-3a87d0ce00ac.png</url>
      <title>DEV Community: Adrian Mageto</title>
      <link>https://dev.to/adrian_mageto</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/adrian_mageto"/>
    <language>en</language>
    <item>
      <title>SQL Window Functions, Joins, CTEs and Subqueries: A Practical Guide Using a Hospital Dataset</title>
      <dc:creator>Adrian Mageto</dc:creator>
      <pubDate>Sun, 18 Oct 2026 12:01:22 +0000</pubDate>
      <link>https://dev.to/adrian_mageto/sql-window-functions-joins-ctes-and-subqueries-a-practical-guide-using-a-hospital-dataset-3ahe</link>
      <guid>https://dev.to/adrian_mageto/sql-window-functions-joins-ctes-and-subqueries-a-practical-guide-using-a-hospital-dataset-3ahe</guid>
      <description>&lt;h2&gt;
  
  
  Introduction
&lt;/h2&gt;

&lt;p&gt;Most SQL tutorials stop at &lt;code&gt;SELECT&lt;/code&gt;, &lt;code&gt;WHERE&lt;/code&gt; and &lt;code&gt;GROUP BY&lt;/code&gt;. Real analysis needs more: ranking patients, comparing a visit with the previous one, combining several tables, and breaking a hard question into steps. This article covers four tools that make that possible:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;Window functions&lt;/strong&gt;: calculations across related rows without collapsing them.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Joins&lt;/strong&gt;: combining tables.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Common Table Expressions (CTEs)&lt;/strong&gt;: naming intermediate results.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Subqueries&lt;/strong&gt;: queries nested inside other queries.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Every example uses one hospital database (PostgreSQL syntax), and every result shown comes from running the query on that data.&lt;/p&gt;

&lt;h2&gt;
  
  
  The hospital dataset
&lt;/h2&gt;

&lt;p&gt;The database lives in a schema called &lt;code&gt;morning&lt;/code&gt; and has a star-style design: one fact table (&lt;code&gt;visits&lt;/code&gt;) and several dimension tables.&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Table&lt;/th&gt;
&lt;th&gt;What it holds&lt;/th&gt;
&lt;th&gt;Rows&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;patients&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;patient_id, name, gender, age, county, sub_county&lt;/td&gt;
&lt;td&gt;25&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;doctors&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;doctor_id, name, gender, specialty, experience years&lt;/td&gt;
&lt;td&gt;4&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;department&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;department_id, department_name&lt;/td&gt;
&lt;td&gt;4&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;wards&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;ward_id, ward_name&lt;/td&gt;
&lt;td&gt;4&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;diagnosis&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;diagnosis_id, name, category&lt;/td&gt;
&lt;td&gt;18&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;procedures&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;procedure_id, name, category&lt;/td&gt;
&lt;td&gt;15&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;visits&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;one row per visit: date, patient, doctor, department, ward, diagnosis, procedure, admission_type, length_of_stay_days, triage_level, insurance_provider, total_bill_kes, payment_method, follow_up_required, patient_satisfaction_score&lt;/td&gt;
&lt;td&gt;30&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Before running anything:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;set&lt;/span&gt; &lt;span class="n"&gt;search_path&lt;/span&gt; &lt;span class="k"&gt;to&lt;/span&gt; &lt;span class="n"&gt;morning&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Data quality notes.&lt;/strong&gt; While working with the data I noticed a few things that affect grouping and joining: diagnosis names such as &lt;code&gt;Back Pian&lt;/code&gt; and the category &lt;code&gt;Respiratoey Disease&lt;/code&gt; contain typos; &lt;code&gt;Infectious&lt;/code&gt; and &lt;code&gt;Infectious Disease&lt;/code&gt; are two spellings of one category; one county is stored with a leading space (&lt;code&gt;Kiambu&lt;/code&gt;); and diagnoses 513 to 518 were missing from the original list and were added with suggested names. In real work you would clean these first, because &lt;code&gt;GROUP BY&lt;/code&gt; treats each spelling as a separate group.&lt;/p&gt;




&lt;h2&gt;
  
  
  Part 1: Window Functions
&lt;/h2&gt;

&lt;h3&gt;
  
  
  What is a window function?
&lt;/h3&gt;

&lt;p&gt;An aggregate with &lt;code&gt;GROUP BY&lt;/code&gt; collapses many rows into one. A &lt;strong&gt;window function&lt;/strong&gt; performs a calculation across a set of related rows (the &lt;em&gt;window&lt;/em&gt;) but keeps every original row. That lets you show a visit and, on the same row, its rank, the previous bill, or a running total.&lt;/p&gt;

&lt;h3&gt;
  
  
  The OVER() clause
&lt;/h3&gt;

&lt;p&gt;Every window function is followed by &lt;code&gt;OVER (...)&lt;/code&gt;, which defines the window:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="n"&gt;function_name&lt;/span&gt;&lt;span class="p"&gt;(...)&lt;/span&gt; &lt;span class="n"&gt;OVER&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;PARTITION&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="k"&gt;column&lt;/span&gt;   &lt;span class="c1"&gt;-- split rows into groups (optional)&lt;/span&gt;
    &lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="k"&gt;column&lt;/span&gt;       &lt;span class="c1"&gt;-- order rows inside each group (optional)&lt;/span&gt;
    &lt;span class="n"&gt;frame_clause&lt;/span&gt;          &lt;span class="c1"&gt;-- which rows around the current row to use (optional)&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;&lt;code&gt;OVER ()&lt;/code&gt;&lt;/strong&gt; with nothing inside means the window is the whole result set.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;&lt;code&gt;PARTITION BY&lt;/code&gt;&lt;/strong&gt; restarts the calculation for each group, like &lt;code&gt;GROUP BY&lt;/code&gt; but without collapsing rows. Partition by &lt;code&gt;doctor_id&lt;/code&gt; and each doctor gets their own ranking.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;&lt;code&gt;ORDER BY&lt;/code&gt;&lt;/strong&gt; inside &lt;code&gt;OVER&lt;/code&gt; decides the order the function sees rows in. It matters for ranking, &lt;code&gt;LAG&lt;/code&gt;/&lt;code&gt;LEAD&lt;/code&gt;, and running totals. It is separate from the &lt;code&gt;ORDER BY&lt;/code&gt; at the end of the query, which only sorts the final output.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Window frame&lt;/strong&gt; says exactly which rows, relative to the current row, are included (covered in detail below).&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  1. ROW_NUMBER()
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;What it does:&lt;/strong&gt; gives each row in a partition a unique number, 1, 2, 3, and so on, following the &lt;code&gt;ORDER BY&lt;/code&gt;. Even tied rows get different numbers.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Why use it:&lt;/strong&gt; numbering visits, picking the latest record per patient, removing duplicates.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Hospital scenario:&lt;/strong&gt; number each patient's visits in date order, for the five patients who came back more than once.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;patient_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;row_number&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;partition&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;patient_id&lt;/span&gt; &lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;visit_number&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;patient_id&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;2&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;5&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;7&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;10&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;patient_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;What it does:&lt;/strong&gt; it splits the rows into one group per patient, sorts each group by date, and numbers them from 1.&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;visit_id&lt;/th&gt;
&lt;th&gt;patient_id&lt;/th&gt;
&lt;th&gt;visit_date&lt;/th&gt;
&lt;th&gt;total_bill_kes&lt;/th&gt;
&lt;th&gt;visit_number&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;1001&lt;/td&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;td&gt;2026-06-01&lt;/td&gt;
&lt;td&gt;3500&lt;/td&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1011&lt;/td&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;td&gt;2026-06-06&lt;/td&gt;
&lt;td&gt;4200&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1002&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;2026-06-01&lt;/td&gt;
&lt;td&gt;2500&lt;/td&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1021&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;2026-06-11&lt;/td&gt;
&lt;td&gt;3800&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1005&lt;/td&gt;
&lt;td&gt;5&lt;/td&gt;
&lt;td&gt;2026-06-03&lt;/td&gt;
&lt;td&gt;4000&lt;/td&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1025&lt;/td&gt;
&lt;td&gt;5&lt;/td&gt;
&lt;td&gt;2026-06-13&lt;/td&gt;
&lt;td&gt;7600&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1007&lt;/td&gt;
&lt;td&gt;7&lt;/td&gt;
&lt;td&gt;2026-06-04&lt;/td&gt;
&lt;td&gt;5500&lt;/td&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1028&lt;/td&gt;
&lt;td&gt;7&lt;/td&gt;
&lt;td&gt;2026-06-14&lt;/td&gt;
&lt;td&gt;1900&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1010&lt;/td&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;td&gt;2026-06-05&lt;/td&gt;
&lt;td&gt;76000&lt;/td&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1030&lt;/td&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;td&gt;2026-06-15&lt;/td&gt;
&lt;td&gt;22000&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;&lt;strong&gt;A very common pattern: the latest visit per patient.&lt;/strong&gt; You cannot filter on a window function directly in &lt;code&gt;WHERE&lt;/code&gt;, so wrap it in a CTE (explained in Part 3):&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;ranked&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;patient_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
           &lt;span class="n"&gt;row_number&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;partition&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;patient_id&lt;/span&gt; &lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt; &lt;span class="k"&gt;desc&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;visit_id&lt;/span&gt; &lt;span class="k"&gt;desc&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;rn&lt;/span&gt;
    &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;patient_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;ranked&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;rn&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;This returns exactly one row per patient (25 rows), the most recent visit of each.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. RANK() and 3. DENSE_RANK() (compared with ROW_NUMBER)
&lt;/h2&gt;

&lt;p&gt;These three functions all rank rows, but they handle &lt;strong&gt;ties&lt;/strong&gt; differently:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Function&lt;/th&gt;
&lt;th&gt;Ties get&lt;/th&gt;
&lt;th&gt;After a tie&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;ROW_NUMBER()&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;different numbers (arbitrary unless you add a tie-breaker)&lt;/td&gt;
&lt;td&gt;continues 1, 2, 3, 4&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;RANK()&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;the same rank&lt;/td&gt;
&lt;td&gt;
&lt;strong&gt;skips&lt;/strong&gt; numbers (1, 2, 2, 4)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;DENSE_RANK()&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;the same rank&lt;/td&gt;
&lt;td&gt;
&lt;strong&gt;no gaps&lt;/strong&gt; (1, 2, 2, 3)&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;&lt;strong&gt;Hospital scenario:&lt;/strong&gt; rank inpatient visits by length of stay, longest first. Several patients stayed the same number of days, so ties appear.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;patient_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;length_of_stay_days&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;stay&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;row_number&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;length_of_stay_days&lt;/span&gt; &lt;span class="k"&gt;desc&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;row_num&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;rank&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt;       &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;length_of_stay_days&lt;/span&gt; &lt;span class="k"&gt;desc&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;           &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;rnk&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;dense_rank&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;length_of_stay_days&lt;/span&gt; &lt;span class="k"&gt;desc&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;           &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;dense_rnk&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;admission_type&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'Inpatient'&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;row_num&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;visit_id&lt;/th&gt;
&lt;th&gt;patient_id&lt;/th&gt;
&lt;th&gt;stay&lt;/th&gt;
&lt;th&gt;row_num&lt;/th&gt;
&lt;th&gt;rnk&lt;/th&gt;
&lt;th&gt;dense_rnk&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;1017&lt;/td&gt;
&lt;td&gt;16&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1010&lt;/td&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;td&gt;5&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1004&lt;/td&gt;
&lt;td&gt;4&lt;/td&gt;
&lt;td&gt;4&lt;/td&gt;
&lt;td&gt;3&lt;/td&gt;
&lt;td&gt;3&lt;/td&gt;
&lt;td&gt;3&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1003&lt;/td&gt;
&lt;td&gt;3&lt;/td&gt;
&lt;td&gt;3&lt;/td&gt;
&lt;td&gt;4&lt;/td&gt;
&lt;td&gt;4&lt;/td&gt;
&lt;td&gt;4&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1016&lt;/td&gt;
&lt;td&gt;15&lt;/td&gt;
&lt;td&gt;3&lt;/td&gt;
&lt;td&gt;5&lt;/td&gt;
&lt;td&gt;4&lt;/td&gt;
&lt;td&gt;4&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1015&lt;/td&gt;
&lt;td&gt;14&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;td&gt;5&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1024&lt;/td&gt;
&lt;td&gt;22&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;7&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;td&gt;5&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1026&lt;/td&gt;
&lt;td&gt;23&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;8&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;td&gt;5&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1027&lt;/td&gt;
&lt;td&gt;24&lt;/td&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;td&gt;9&lt;/td&gt;
&lt;td&gt;9&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;&lt;strong&gt;Reading the result:&lt;/strong&gt; visits 1003 and 1016 both stayed 3 days, so &lt;code&gt;RANK&lt;/code&gt; gives both rank 4, and the next group starts at rank 6, because positions 4 and 5 were used. &lt;code&gt;DENSE_RANK&lt;/code&gt; gives the next group rank 5 with no gap. &lt;code&gt;ROW_NUMBER&lt;/code&gt; simply counts 1 to 9.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;When to use which:&lt;/strong&gt; &lt;code&gt;ROW_NUMBER&lt;/code&gt; when you need exactly one row per group (de-duplication). &lt;code&gt;RANK&lt;/code&gt; for competition-style ranking where gaps are meaningful. &lt;code&gt;DENSE_RANK&lt;/code&gt; for 'top N levels' questions, such as 'the three longest distinct stay lengths'.&lt;/p&gt;

&lt;h2&gt;
  
  
  4. NTILE(n)
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;What it does:&lt;/strong&gt; divides the ordered rows into &lt;code&gt;n&lt;/code&gt; roughly equal buckets and tells you which bucket each row falls in. If the rows do not divide evenly, the first buckets get one extra row.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Why use it:&lt;/strong&gt; quartiles, percentile-style bands, segmenting customers or patients into tiers.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Hospital scenario:&lt;/strong&gt; split all 30 visits into four billing quartiles and see the bill range of each.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;t&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
           &lt;span class="n"&gt;ntile&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;4&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;quartile&lt;/span&gt;
    &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;quartile&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;count&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;min&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;lowest&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;max&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;highest&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;t&lt;/span&gt;
&lt;span class="k"&gt;group&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;quartile&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;quartile&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;quartile&lt;/th&gt;
&lt;th&gt;visits&lt;/th&gt;
&lt;th&gt;lowest&lt;/th&gt;
&lt;th&gt;highest&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;td&gt;8&lt;/td&gt;
&lt;td&gt;1800&lt;/td&gt;
&lt;td&gt;3500&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;8&lt;/td&gt;
&lt;td&gt;3800&lt;/td&gt;
&lt;td&gt;6500&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;3&lt;/td&gt;
&lt;td&gt;7&lt;/td&gt;
&lt;td&gt;7000&lt;/td&gt;
&lt;td&gt;24000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;4&lt;/td&gt;
&lt;td&gt;7&lt;/td&gt;
&lt;td&gt;28000&lt;/td&gt;
&lt;td&gt;82000&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Thirty rows cannot split into four equal groups, so the first two quartiles hold 8 rows and the last two hold 7. The top quartile (bills of KES 28,000 and above) is where most of the hospital's revenue sits.&lt;/p&gt;

&lt;h2&gt;
  
  
  5. LAG() and 6. LEAD()
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;What they do:&lt;/strong&gt; &lt;code&gt;LAG(col)&lt;/code&gt; returns the value of &lt;code&gt;col&lt;/code&gt; from the &lt;strong&gt;previous&lt;/strong&gt; row in the window; &lt;code&gt;LEAD(col)&lt;/code&gt; returns it from the &lt;strong&gt;next&lt;/strong&gt; row. The first row of a partition has no previous row, so &lt;code&gt;LAG&lt;/code&gt; returns &lt;code&gt;NULL&lt;/code&gt; there; the last row gets &lt;code&gt;NULL&lt;/code&gt; from &lt;code&gt;LEAD&lt;/code&gt;. An optional second argument sets the offset and a third a default (&lt;code&gt;LAG(col, 2, 0)&lt;/code&gt;).&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Why use them:&lt;/strong&gt; comparing a record with its neighbours, such as change since last visit, time between events, month-over-month growth, without a self join.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Hospital scenario:&lt;/strong&gt; for returning patients, show how the bill changed compared with their previous visit, and the date of the next visit.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;patient_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;lag&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="n"&gt;w&lt;/span&gt;                       &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;previous_bill&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;total_bill_kes&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="n"&gt;lag&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="n"&gt;w&lt;/span&gt;      &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;bill_change&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;lead&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;visit_date&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="n"&gt;w&lt;/span&gt;                          &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;next_visit&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;patient_id&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;2&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;5&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;7&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;10&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;window&lt;/span&gt; &lt;span class="n"&gt;w&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;partition&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;patient_id&lt;/span&gt; &lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;patient_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The &lt;code&gt;WINDOW w AS (...)&lt;/code&gt; clause names a window definition once so several functions can reuse it.&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;patient_id&lt;/th&gt;
&lt;th&gt;visit_id&lt;/th&gt;
&lt;th&gt;visit_date&lt;/th&gt;
&lt;th&gt;total_bill_kes&lt;/th&gt;
&lt;th&gt;previous_bill&lt;/th&gt;
&lt;th&gt;bill_change&lt;/th&gt;
&lt;th&gt;next_visit&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;td&gt;1001&lt;/td&gt;
&lt;td&gt;2026-06-01&lt;/td&gt;
&lt;td&gt;3500&lt;/td&gt;
&lt;td&gt;NULL&lt;/td&gt;
&lt;td&gt;NULL&lt;/td&gt;
&lt;td&gt;2026-06-06&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;td&gt;1011&lt;/td&gt;
&lt;td&gt;2026-06-06&lt;/td&gt;
&lt;td&gt;4200&lt;/td&gt;
&lt;td&gt;3500&lt;/td&gt;
&lt;td&gt;700&lt;/td&gt;
&lt;td&gt;NULL&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;5&lt;/td&gt;
&lt;td&gt;1005&lt;/td&gt;
&lt;td&gt;2026-06-03&lt;/td&gt;
&lt;td&gt;4000&lt;/td&gt;
&lt;td&gt;NULL&lt;/td&gt;
&lt;td&gt;NULL&lt;/td&gt;
&lt;td&gt;2026-06-13&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;5&lt;/td&gt;
&lt;td&gt;1025&lt;/td&gt;
&lt;td&gt;2026-06-13&lt;/td&gt;
&lt;td&gt;7600&lt;/td&gt;
&lt;td&gt;4000&lt;/td&gt;
&lt;td&gt;3600&lt;/td&gt;
&lt;td&gt;NULL&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;7&lt;/td&gt;
&lt;td&gt;1007&lt;/td&gt;
&lt;td&gt;2026-06-04&lt;/td&gt;
&lt;td&gt;5500&lt;/td&gt;
&lt;td&gt;NULL&lt;/td&gt;
&lt;td&gt;NULL&lt;/td&gt;
&lt;td&gt;2026-06-14&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;7&lt;/td&gt;
&lt;td&gt;1028&lt;/td&gt;
&lt;td&gt;2026-06-14&lt;/td&gt;
&lt;td&gt;1900&lt;/td&gt;
&lt;td&gt;5500&lt;/td&gt;
&lt;td&gt;-3600&lt;/td&gt;
&lt;td&gt;NULL&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;td&gt;1010&lt;/td&gt;
&lt;td&gt;2026-06-05&lt;/td&gt;
&lt;td&gt;76000&lt;/td&gt;
&lt;td&gt;NULL&lt;/td&gt;
&lt;td&gt;NULL&lt;/td&gt;
&lt;td&gt;2026-06-15&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;td&gt;1030&lt;/td&gt;
&lt;td&gt;2026-06-15&lt;/td&gt;
&lt;td&gt;22000&lt;/td&gt;
&lt;td&gt;76000&lt;/td&gt;
&lt;td&gt;-54000&lt;/td&gt;
&lt;td&gt;NULL&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;(Patient 2 follows the same pattern: 2500 then 3800, a change of 1300.) Patient 10's bill dropped by KES 54,000: the first visit was a five-day inpatient stay for heart failure, the second a one-day emergency visit for chest pain. The change per visit tells a story that a plain &lt;code&gt;SUM&lt;/code&gt; would hide.&lt;/p&gt;

&lt;h2&gt;
  
  
  7. FIRST_VALUE() and 8. LAST_VALUE()
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;What they do:&lt;/strong&gt; return the first or last value of the window frame. This is where &lt;strong&gt;window frames&lt;/strong&gt; matter most, so read the warning below.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Hospital scenario:&lt;/strong&gt; for Dr. Faith Chebet (doctor 104), compare each visit's bill with her first and last bill.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;first_value&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="n"&gt;w&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;first_bill&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;last_value&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;  &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="n"&gt;w&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;last_bill_default&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;last_value&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;  &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
           &lt;span class="k"&gt;partition&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;doctor_id&lt;/span&gt; &lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;visit_id&lt;/span&gt;
           &lt;span class="k"&gt;rows&lt;/span&gt; &lt;span class="k"&gt;between&lt;/span&gt; &lt;span class="n"&gt;unbounded&lt;/span&gt; &lt;span class="k"&gt;preceding&lt;/span&gt; &lt;span class="k"&gt;and&lt;/span&gt; &lt;span class="n"&gt;unbounded&lt;/span&gt; &lt;span class="k"&gt;following&lt;/span&gt;
       &lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;last_bill_correct&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;doctor_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;104&lt;/span&gt;
&lt;span class="k"&gt;window&lt;/span&gt; &lt;span class="n"&gt;w&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;partition&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;doctor_id&lt;/span&gt; &lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;visit_id&lt;/th&gt;
&lt;th&gt;visit_date&lt;/th&gt;
&lt;th&gt;total_bill_kes&lt;/th&gt;
&lt;th&gt;first_bill&lt;/th&gt;
&lt;th&gt;last_bill_default&lt;/th&gt;
&lt;th&gt;last_bill_correct&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;1004&lt;/td&gt;
&lt;td&gt;2026-06-02&lt;/td&gt;
&lt;td&gt;42000&lt;/td&gt;
&lt;td&gt;42000&lt;/td&gt;
&lt;td&gt;42000&lt;/td&gt;
&lt;td&gt;1900&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1007&lt;/td&gt;
&lt;td&gt;2026-06-04&lt;/td&gt;
&lt;td&gt;5500&lt;/td&gt;
&lt;td&gt;42000&lt;/td&gt;
&lt;td&gt;5500&lt;/td&gt;
&lt;td&gt;1900&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1014&lt;/td&gt;
&lt;td&gt;2026-06-07&lt;/td&gt;
&lt;td&gt;1800&lt;/td&gt;
&lt;td&gt;42000&lt;/td&gt;
&lt;td&gt;1800&lt;/td&gt;
&lt;td&gt;1900&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1019&lt;/td&gt;
&lt;td&gt;2026-06-10&lt;/td&gt;
&lt;td&gt;5200&lt;/td&gt;
&lt;td&gt;42000&lt;/td&gt;
&lt;td&gt;5200&lt;/td&gt;
&lt;td&gt;1900&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1024&lt;/td&gt;
&lt;td&gt;2026-06-12&lt;/td&gt;
&lt;td&gt;18500&lt;/td&gt;
&lt;td&gt;42000&lt;/td&gt;
&lt;td&gt;18500&lt;/td&gt;
&lt;td&gt;1900&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1028&lt;/td&gt;
&lt;td&gt;2026-06-14&lt;/td&gt;
&lt;td&gt;1900&lt;/td&gt;
&lt;td&gt;42000&lt;/td&gt;
&lt;td&gt;1900&lt;/td&gt;
&lt;td&gt;1900&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;&lt;strong&gt;The trap:&lt;/strong&gt; when a window has an &lt;code&gt;ORDER BY&lt;/code&gt; and no explicit frame, the default frame runs from the start of the partition &lt;strong&gt;to the current row&lt;/strong&gt;. So &lt;code&gt;LAST_VALUE&lt;/code&gt; with the default frame just returns the current row's own value (the &lt;code&gt;last_bill_default&lt;/code&gt; column), which is almost never what you want. Extending the frame to &lt;code&gt;UNBOUNDED FOLLOWING&lt;/code&gt; makes it see the real last row (&lt;code&gt;last_bill_correct&lt;/code&gt;). &lt;code&gt;FIRST_VALUE&lt;/code&gt; is unaffected because the start of the frame is always the partition start.&lt;/p&gt;

&lt;h2&gt;
  
  
  9. Aggregate window functions: SUM, AVG, COUNT, MIN, MAX
&lt;/h2&gt;

&lt;p&gt;The familiar aggregates become window functions when you add &lt;code&gt;OVER&lt;/code&gt;. Without &lt;code&gt;ORDER BY&lt;/code&gt; they cover the whole partition on every row; with &lt;code&gt;ORDER BY&lt;/code&gt; they become running calculations.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Hospital scenario:&lt;/strong&gt; show each department's totals while keeping the context of every visit. Here &lt;code&gt;SELECT DISTINCT&lt;/code&gt; is used to display one line per department.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="k"&gt;distinct&lt;/span&gt; &lt;span class="n"&gt;department_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="k"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;            &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;partition&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;department_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;dept_revenue&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;round&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;avg&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;  &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;partition&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;department_id&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;dept_avg_bill&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="k"&gt;count&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;                       &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;partition&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;department_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;visit_count&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="k"&gt;min&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;            &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;partition&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;department_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;lowest_bill&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="k"&gt;max&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;            &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;partition&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;department_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;highest_bill&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;department_id&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;department_id&lt;/th&gt;
&lt;th&gt;dept_revenue&lt;/th&gt;
&lt;th&gt;dept_avg_bill&lt;/th&gt;
&lt;th&gt;visit_count&lt;/th&gt;
&lt;th&gt;lowest_bill&lt;/th&gt;
&lt;th&gt;highest_bill&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;10 (Cardiology)&lt;/td&gt;
&lt;td&gt;210100&lt;/td&gt;
&lt;td&gt;23344&lt;/td&gt;
&lt;td&gt;9&lt;/td&gt;
&lt;td&gt;3500&lt;/td&gt;
&lt;td&gt;82000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;20 (General Medicine)&lt;/td&gt;
&lt;td&gt;97800&lt;/td&gt;
&lt;td&gt;10867&lt;/td&gt;
&lt;td&gt;9&lt;/td&gt;
&lt;td&gt;2000&lt;/td&gt;
&lt;td&gt;36000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;30 (Orthopedics)&lt;/td&gt;
&lt;td&gt;105200&lt;/td&gt;
&lt;td&gt;17533&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;td&gt;6500&lt;/td&gt;
&lt;td&gt;33000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;40 (Pediatrics)&lt;/td&gt;
&lt;td&gt;74900&lt;/td&gt;
&lt;td&gt;12483&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;td&gt;1800&lt;/td&gt;
&lt;td&gt;42000&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Without &lt;code&gt;DISTINCT&lt;/code&gt;, every one of the 30 visit rows would carry its department's figures next to its own bill, which is perfect for questions like 'how does this bill compare with its department average?'&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Running totals and moving averages&lt;/strong&gt; use &lt;code&gt;ORDER BY&lt;/code&gt; and a frame. Here we total each day's revenue first, then accumulate:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;daily&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;revenue&lt;/span&gt;
    &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;
    &lt;span class="k"&gt;group&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;revenue&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="k"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;revenue&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;running_total&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;round&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;avg&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;revenue&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;
             &lt;span class="k"&gt;rows&lt;/span&gt; &lt;span class="k"&gt;between&lt;/span&gt; &lt;span class="mi"&gt;2&lt;/span&gt; &lt;span class="k"&gt;preceding&lt;/span&gt; &lt;span class="k"&gt;and&lt;/span&gt; &lt;span class="k"&gt;current&lt;/span&gt; &lt;span class="k"&gt;row&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;moving_avg_3day&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;daily&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;First five of the 15 rows:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;visit_date&lt;/th&gt;
&lt;th&gt;revenue&lt;/th&gt;
&lt;th&gt;running_total&lt;/th&gt;
&lt;th&gt;moving_avg_3day&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;2026-06-01&lt;/td&gt;
&lt;td&gt;6000&lt;/td&gt;
&lt;td&gt;6000&lt;/td&gt;
&lt;td&gt;6000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;2026-06-02&lt;/td&gt;
&lt;td&gt;70000&lt;/td&gt;
&lt;td&gt;76000&lt;/td&gt;
&lt;td&gt;38000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;2026-06-03&lt;/td&gt;
&lt;td&gt;7000&lt;/td&gt;
&lt;td&gt;83000&lt;/td&gt;
&lt;td&gt;27667&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;2026-06-04&lt;/td&gt;
&lt;td&gt;12000&lt;/td&gt;
&lt;td&gt;95000&lt;/td&gt;
&lt;td&gt;29667&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;2026-06-05&lt;/td&gt;
&lt;td&gt;91000&lt;/td&gt;
&lt;td&gt;186000&lt;/td&gt;
&lt;td&gt;36667&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The moving average on day 2 is (6000 + 70000) / 2 because only two rows exist so far; from day 3 on it averages the current day and the two before.&lt;/p&gt;

&lt;h2&gt;
  
  
  Window frames in depth: ROWS vs RANGE
&lt;/h2&gt;

&lt;p&gt;The frame clause has this shape:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="p"&gt;{&lt;/span&gt; &lt;span class="k"&gt;ROWS&lt;/span&gt; &lt;span class="o"&gt;|&lt;/span&gt; &lt;span class="k"&gt;RANGE&lt;/span&gt; &lt;span class="p"&gt;}&lt;/span&gt; &lt;span class="k"&gt;BETWEEN&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt;&lt;span class="k"&gt;start&lt;/span&gt;&lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt;&lt;span class="k"&gt;end&lt;/span&gt;&lt;span class="o"&gt;&amp;gt;&lt;/span&gt;
&lt;span class="c1"&gt;-- start/end can be: UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;&lt;code&gt;ROWS&lt;/code&gt;&lt;/strong&gt; counts physical rows.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;&lt;code&gt;RANGE&lt;/code&gt;&lt;/strong&gt; works on values: all rows with the same &lt;code&gt;ORDER BY&lt;/code&gt; value (peers) are treated as one unit.&lt;/li&gt;
&lt;li&gt;The default, when &lt;code&gt;ORDER BY&lt;/code&gt; is present, is &lt;code&gt;RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW&lt;/code&gt;.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The hospital data has two visits on most days, which shows the difference clearly:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="k"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;range_default&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="k"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;visit_id&lt;/span&gt;
             &lt;span class="k"&gt;rows&lt;/span&gt; &lt;span class="k"&gt;between&lt;/span&gt; &lt;span class="n"&gt;unbounded&lt;/span&gt; &lt;span class="k"&gt;preceding&lt;/span&gt; &lt;span class="k"&gt;and&lt;/span&gt; &lt;span class="k"&gt;current&lt;/span&gt; &lt;span class="k"&gt;row&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;rows_running&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;=&lt;/span&gt; &lt;span class="s1"&gt;'2026-06-02'&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;visit_date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;visit_id&lt;/th&gt;
&lt;th&gt;visit_date&lt;/th&gt;
&lt;th&gt;total_bill_kes&lt;/th&gt;
&lt;th&gt;range_default&lt;/th&gt;
&lt;th&gt;rows_running&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;1001&lt;/td&gt;
&lt;td&gt;2026-06-01&lt;/td&gt;
&lt;td&gt;3500&lt;/td&gt;
&lt;td&gt;6000&lt;/td&gt;
&lt;td&gt;3500&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1002&lt;/td&gt;
&lt;td&gt;2026-06-01&lt;/td&gt;
&lt;td&gt;2500&lt;/td&gt;
&lt;td&gt;6000&lt;/td&gt;
&lt;td&gt;6000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1003&lt;/td&gt;
&lt;td&gt;2026-06-02&lt;/td&gt;
&lt;td&gt;28000&lt;/td&gt;
&lt;td&gt;76000&lt;/td&gt;
&lt;td&gt;34000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1004&lt;/td&gt;
&lt;td&gt;2026-06-02&lt;/td&gt;
&lt;td&gt;42000&lt;/td&gt;
&lt;td&gt;76000&lt;/td&gt;
&lt;td&gt;76000&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;With &lt;code&gt;RANGE&lt;/code&gt;, both visits on 1 June are peers, so each row already includes the other and shows 6000. With &lt;code&gt;ROWS&lt;/code&gt;, the total grows one row at a time. Use &lt;code&gt;ROWS&lt;/code&gt; for true row-by-row running totals; use &lt;code&gt;RANGE&lt;/code&gt; when rows with the same value should be treated together.&lt;/p&gt;




&lt;h2&gt;
  
  
  Part 2: SQL Joins
&lt;/h2&gt;

&lt;p&gt;A join combines rows from two tables using a condition, usually a foreign key matching a primary key. In our database &lt;code&gt;visits.patient_id&lt;/code&gt; points to &lt;code&gt;patients.patient_id&lt;/code&gt;, &lt;code&gt;visits.doctor_id&lt;/code&gt; to &lt;code&gt;doctors.doctor_id&lt;/code&gt;, and so on.&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Join&lt;/th&gt;
&lt;th&gt;Returns&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;INNER JOIN&lt;/td&gt;
&lt;td&gt;only rows that match in &lt;strong&gt;both&lt;/strong&gt; tables&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;LEFT JOIN&lt;/td&gt;
&lt;td&gt;all rows from the left table, plus matches from the right (NULL if none)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;RIGHT JOIN&lt;/td&gt;
&lt;td&gt;all rows from the right table, plus matches from the left (NULL if none)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;FULL OUTER JOIN&lt;/td&gt;
&lt;td&gt;all rows from both tables, matched where possible, NULL elsewhere&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;CROSS JOIN&lt;/td&gt;
&lt;td&gt;every combination of rows (the Cartesian product)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;SELF JOIN&lt;/td&gt;
&lt;td&gt;a table joined to itself (not a separate keyword; uses aliases)&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h3&gt;
  
  
  INNER JOIN
&lt;/h3&gt;

&lt;p&gt;&lt;strong&gt;Use when&lt;/strong&gt; you only want records that exist on both sides, such as visits together with their patient and doctor details.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Scenario:&lt;/strong&gt; list every inpatient visit with the patient's name and the doctor who handled it.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;doctor_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;
&lt;span class="k"&gt;inner&lt;/span&gt; &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;patients&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt; &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt;
&lt;span class="k"&gt;inner&lt;/span&gt; &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;doctors&lt;/span&gt;  &lt;span class="n"&gt;d&lt;/span&gt; &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;doctor_id&lt;/span&gt;  &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;doctor_id&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;admission_type&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'Inpatient'&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;visit_id&lt;/th&gt;
&lt;th&gt;patient_name&lt;/th&gt;
&lt;th&gt;doctor_name&lt;/th&gt;
&lt;th&gt;total_bill_kes&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;1003&lt;/td&gt;
&lt;td&gt;Grace Naliaka&lt;/td&gt;
&lt;td&gt;Dr. Peter Otieno&lt;/td&gt;
&lt;td&gt;28000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1004&lt;/td&gt;
&lt;td&gt;David Mutua&lt;/td&gt;
&lt;td&gt;Dr. Faith Chebet&lt;/td&gt;
&lt;td&gt;42000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1010&lt;/td&gt;
&lt;td&gt;Peter Ouma&lt;/td&gt;
&lt;td&gt;DR.Brian Mwangi&lt;/td&gt;
&lt;td&gt;76000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1015&lt;/td&gt;
&lt;td&gt;Eunice Moraa&lt;/td&gt;
&lt;td&gt;Dr. Peter Otieno&lt;/td&gt;
&lt;td&gt;24000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1016&lt;/td&gt;
&lt;td&gt;Francis Njuguna&lt;/td&gt;
&lt;td&gt;Dr. Sarah Wanjiku&lt;/td&gt;
&lt;td&gt;36000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1017&lt;/td&gt;
&lt;td&gt;Hellen Jepchirchir&lt;/td&gt;
&lt;td&gt;DR.Brian Mwangi&lt;/td&gt;
&lt;td&gt;82000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1024&lt;/td&gt;
&lt;td&gt;Nadia Hussein&lt;/td&gt;
&lt;td&gt;Dr. Faith Chebet&lt;/td&gt;
&lt;td&gt;18500&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1026&lt;/td&gt;
&lt;td&gt;Patrick Muli&lt;/td&gt;
&lt;td&gt;Dr. Sarah Wanjiku&lt;/td&gt;
&lt;td&gt;29000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1027&lt;/td&gt;
&lt;td&gt;Ruth Nekesa&lt;/td&gt;
&lt;td&gt;Dr. Peter Otieno&lt;/td&gt;
&lt;td&gt;33000&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Nine inpatient visits, each now readable instead of just IDs. Notice that joins can be chained: two joins here, three tables combined.&lt;/p&gt;

&lt;h3&gt;
  
  
  LEFT JOIN
&lt;/h3&gt;

&lt;p&gt;&lt;strong&gt;Use when&lt;/strong&gt; you need every row from the main table even when there is no match: 'all patients, and their inpatient stay if they had one.'&lt;/p&gt;

&lt;p&gt;In this dataset every patient has at least one visit, so a plain left join from &lt;code&gt;patients&lt;/code&gt; to &lt;code&gt;visits&lt;/code&gt; would show no &lt;code&gt;NULL&lt;/code&gt;s. To demonstrate the behaviour realistically, we put the inpatient condition &lt;strong&gt;inside the &lt;code&gt;ON&lt;/code&gt; clause&lt;/strong&gt;:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="k"&gt;count&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;total_rows&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;count&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;matched&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;patients&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;
&lt;span class="k"&gt;left&lt;/span&gt; &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;
       &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt;
      &lt;span class="k"&gt;and&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;admission_type&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'Inpatient'&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;total_rows&lt;/th&gt;
&lt;th&gt;matched&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;25&lt;/td&gt;
&lt;td&gt;9&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;All 25 patients survive; only 9 have a matching inpatient visit, and the other 16 get &lt;code&gt;NULL&lt;/code&gt; in the visit columns. (&lt;code&gt;COUNT(v.visit_id)&lt;/code&gt; ignores &lt;code&gt;NULL&lt;/code&gt;s, which is why it counts only real matches.)&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;The anti-join pattern:&lt;/strong&gt; add &lt;code&gt;WHERE v.visit_id IS NULL&lt;/code&gt; to keep only the patients with &lt;strong&gt;no&lt;/strong&gt; match, meaning patients who have never been admitted:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_name&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;patients&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;
&lt;span class="k"&gt;left&lt;/span&gt; &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;
       &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt;
      &lt;span class="k"&gt;and&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;admission_type&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'Inpatient'&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;visit_id&lt;/span&gt; &lt;span class="k"&gt;is&lt;/span&gt; &lt;span class="k"&gt;null&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;This returns 16 patients, for example Amina Otieno (1), John Kariuki (2) and Esther Achieng (5).&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Important:&lt;/strong&gt; putting the filter in &lt;code&gt;WHERE&lt;/code&gt; instead of &lt;code&gt;ON&lt;/code&gt; would remove the &lt;code&gt;NULL&lt;/code&gt; rows and quietly turn the left join into an inner join. Filters on the &lt;em&gt;right&lt;/em&gt; table belong in &lt;code&gt;ON&lt;/code&gt;.&lt;/p&gt;

&lt;h3&gt;
  
  
  RIGHT JOIN
&lt;/h3&gt;

&lt;p&gt;A right join is a left join with the tables swapped: all rows of the &lt;strong&gt;right&lt;/strong&gt; table are kept. Most people just write a left join and reorder the tables, but it is useful to know.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Scenario:&lt;/strong&gt; list every diagnosis in the hospital's catalogue with the number of &lt;strong&gt;emergency&lt;/strong&gt; visits it had, including diagnoses with none.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;diagnosis_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;count&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;emergency_visits&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;
&lt;span class="k"&gt;right&lt;/span&gt; &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;diagnosis&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;
        &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;diagnosis_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;diagnosis_id&lt;/span&gt;
       &lt;span class="k"&gt;and&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;admission_type&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'Emergency'&lt;/span&gt;
&lt;span class="k"&gt;group&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;diagnosis_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;diagnosis_name&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;emergency_visits&lt;/span&gt; &lt;span class="k"&gt;desc&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;diagnosis_id&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;First rows of the 18 returned:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;diagnosis_name&lt;/th&gt;
&lt;th&gt;emergency_visits&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Chest Pain&lt;/td&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Hypertension&lt;/td&gt;
&lt;td&gt;0&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Malaria&lt;/td&gt;
&lt;td&gt;0&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Fracture&lt;/td&gt;
&lt;td&gt;0&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Only one visit in the data was an emergency (visit 1030, chest pain), but the right join keeps all 18 diagnoses, with a count of zero where nothing matched. Writing &lt;code&gt;from diagnosis d left join visits v ...&lt;/code&gt; would give the identical result.&lt;/p&gt;

&lt;h3&gt;
  
  
  FULL OUTER JOIN
&lt;/h3&gt;

&lt;p&gt;&lt;strong&gt;Use when&lt;/strong&gt; you need everything from both sides and want to see which rows match and which do not: reconciling two lists.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Scenario:&lt;/strong&gt; compare patients who paid cash at least once with patients who have been admitted as inpatients.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;cash&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="k"&gt;distinct&lt;/span&gt; &lt;span class="n"&gt;patient_id&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt; &lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;payment_method&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'Cash'&lt;/span&gt;
&lt;span class="p"&gt;),&lt;/span&gt;
&lt;span class="n"&gt;inpt&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="k"&gt;distinct&lt;/span&gt; &lt;span class="n"&gt;patient_id&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt; &lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;admission_type&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'Inpatient'&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;coalesce&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;patient_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="k"&gt;case&lt;/span&gt; &lt;span class="k"&gt;when&lt;/span&gt; &lt;span class="k"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt; &lt;span class="k"&gt;is&lt;/span&gt; &lt;span class="k"&gt;not&lt;/span&gt; &lt;span class="k"&gt;null&lt;/span&gt; &lt;span class="k"&gt;and&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt; &lt;span class="k"&gt;is&lt;/span&gt; &lt;span class="k"&gt;not&lt;/span&gt; &lt;span class="k"&gt;null&lt;/span&gt; &lt;span class="k"&gt;then&lt;/span&gt; &lt;span class="s1"&gt;'both'&lt;/span&gt;
            &lt;span class="k"&gt;when&lt;/span&gt; &lt;span class="k"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt; &lt;span class="k"&gt;is&lt;/span&gt; &lt;span class="k"&gt;not&lt;/span&gt; &lt;span class="k"&gt;null&lt;/span&gt; &lt;span class="k"&gt;then&lt;/span&gt; &lt;span class="s1"&gt;'cash only'&lt;/span&gt;
            &lt;span class="k"&gt;else&lt;/span&gt; &lt;span class="s1"&gt;'inpatient only'&lt;/span&gt; &lt;span class="k"&gt;end&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;segment&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;cash&lt;/span&gt; &lt;span class="k"&gt;c&lt;/span&gt;
&lt;span class="k"&gt;full&lt;/span&gt; &lt;span class="k"&gt;outer&lt;/span&gt; &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;inpt&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt; &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="k"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;patient_id&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The query returns 17 patients: 8 are &lt;strong&gt;cash only&lt;/strong&gt;, 7 are &lt;strong&gt;inpatient only&lt;/strong&gt; and 2 are in &lt;strong&gt;both&lt;/strong&gt; lists (patients 4, David Mutua, and 22, Nadia Hussein). A full join is the only join that can show 'in A but not B', 'in B but not A' and 'in both' in one pass. &lt;code&gt;COALESCE&lt;/code&gt; picks whichever side has the ID.&lt;/p&gt;

&lt;h3&gt;
  
  
  CROSS JOIN
&lt;/h3&gt;

&lt;p&gt;A cross join pairs &lt;strong&gt;every&lt;/strong&gt; row of one table with &lt;strong&gt;every&lt;/strong&gt; row of the other: 4 doctors x 3 admission types = 12 rows. It has no &lt;code&gt;ON&lt;/code&gt; clause.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Use when&lt;/strong&gt; you need to generate all combinations, typically to build a complete grid so that combinations with zero activity still appear in a report.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;doctor_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;t&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;admission_type&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;count&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;doctors&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;
&lt;span class="k"&gt;cross&lt;/span&gt; &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="s1"&gt;'Outpatient'&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;admission_type&lt;/span&gt;
            &lt;span class="k"&gt;union&lt;/span&gt; &lt;span class="k"&gt;all&lt;/span&gt; &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="s1"&gt;'Inpatient'&lt;/span&gt;
            &lt;span class="k"&gt;union&lt;/span&gt; &lt;span class="k"&gt;all&lt;/span&gt; &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="s1"&gt;'Emergency'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;t&lt;/span&gt;
&lt;span class="k"&gt;left&lt;/span&gt; &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;
       &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;doctor_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;doctor_id&lt;/span&gt;
      &lt;span class="k"&gt;and&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;admission_type&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;t&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;admission_type&lt;/span&gt;
&lt;span class="k"&gt;group&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;doctor_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;doctor_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;t&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;admission_type&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;doctor_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;t&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;admission_type&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;First six of the 12 rows:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;doctor_name&lt;/th&gt;
&lt;th&gt;admission_type&lt;/th&gt;
&lt;th&gt;visits&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;DR.Brian Mwangi&lt;/td&gt;
&lt;td&gt;Emergency&lt;/td&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;DR.Brian Mwangi&lt;/td&gt;
&lt;td&gt;Inpatient&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;DR.Brian Mwangi&lt;/td&gt;
&lt;td&gt;Outpatient&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Dr. Sarah Wanjiku&lt;/td&gt;
&lt;td&gt;Emergency&lt;/td&gt;
&lt;td&gt;0&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Dr. Sarah Wanjiku&lt;/td&gt;
&lt;td&gt;Inpatient&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Dr. Sarah Wanjiku&lt;/td&gt;
&lt;td&gt;Outpatient&lt;/td&gt;
&lt;td&gt;7&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The zero rows are the point: a normal &lt;code&gt;GROUP BY&lt;/code&gt; would silently drop 'Dr. Sarah Wanjiku, Emergency' because no such visit exists. &lt;strong&gt;Caution:&lt;/strong&gt; a cross join of two big tables explodes (1,000 x 1,000 = 1,000,000 rows), so use it deliberately.&lt;/p&gt;

&lt;h3&gt;
  
  
  SELF JOIN
&lt;/h3&gt;

&lt;p&gt;A self join joins a table to itself, using two aliases so SQL can tell the copies apart. It suits comparisons between rows of the same table.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Scenario A:&lt;/strong&gt; find pairs of patients from the same county (to plan a shared outreach clinic).&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;a&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_name&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;patient_1&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_name&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;patient_2&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;a&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;county&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;patients&lt;/span&gt; &lt;span class="n"&gt;a&lt;/span&gt;
&lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;patients&lt;/span&gt; &lt;span class="n"&gt;b&lt;/span&gt;
  &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;a&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;county&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;county&lt;/span&gt;
 &lt;span class="k"&gt;and&lt;/span&gt; &lt;span class="n"&gt;a&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt; &lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;patient_1&lt;/th&gt;
&lt;th&gt;patient_2&lt;/th&gt;
&lt;th&gt;county&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Esther Achieng&lt;/td&gt;
&lt;td&gt;Brian Omondi&lt;/td&gt;
&lt;td&gt;Kisumu&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Amina Otieno&lt;/td&gt;
&lt;td&gt;Isaac Kamau&lt;/td&gt;
&lt;td&gt;Nairobi&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The condition &lt;code&gt;a.patient_id &amp;lt; b.patient_id&lt;/code&gt; stops a patient being paired with themselves and prevents the same pair appearing twice in reverse order.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Scenario B:&lt;/strong&gt; measure how many days passed between a patient's visits (re-visit analysis).&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;v1&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;v1&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;visit_id&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;first_visit&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;v2&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;visit_id&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;return_visit&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;v2&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;visit_date&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="n"&gt;v1&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;visit_date&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;days_between&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt; &lt;span class="n"&gt;v1&lt;/span&gt;
&lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt; &lt;span class="n"&gt;v2&lt;/span&gt;
  &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;v1&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;v2&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt;
 &lt;span class="k"&gt;and&lt;/span&gt; &lt;span class="n"&gt;v2&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;visit_date&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;v1&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;visit_date&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;v1&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;patient_id&lt;/th&gt;
&lt;th&gt;first_visit&lt;/th&gt;
&lt;th&gt;return_visit&lt;/th&gt;
&lt;th&gt;days_between&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;td&gt;1001&lt;/td&gt;
&lt;td&gt;1011&lt;/td&gt;
&lt;td&gt;5&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;1002&lt;/td&gt;
&lt;td&gt;1021&lt;/td&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;5&lt;/td&gt;
&lt;td&gt;1005&lt;/td&gt;
&lt;td&gt;1025&lt;/td&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;7&lt;/td&gt;
&lt;td&gt;1007&lt;/td&gt;
&lt;td&gt;1028&lt;/td&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;td&gt;1010&lt;/td&gt;
&lt;td&gt;1030&lt;/td&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The same answer is possible with &lt;code&gt;LAG()&lt;/code&gt;, which is usually cleaner and faster. A self join is still the right tool when you need to compare &lt;em&gt;every&lt;/em&gt; pair of rows, not just neighbours.&lt;/p&gt;

&lt;h3&gt;
  
  
  Choosing the right join
&lt;/h3&gt;

&lt;ul&gt;
&lt;li&gt;Need only matched records? &lt;strong&gt;INNER&lt;/strong&gt;.&lt;/li&gt;
&lt;li&gt;Need all of the main table, matched or not? &lt;strong&gt;LEFT&lt;/strong&gt; (and swap the tables instead of using RIGHT).&lt;/li&gt;
&lt;li&gt;Need to find rows with &lt;strong&gt;no&lt;/strong&gt; match? &lt;strong&gt;LEFT + &lt;code&gt;IS NULL&lt;/code&gt;&lt;/strong&gt;.&lt;/li&gt;
&lt;li&gt;Reconciling two sources? &lt;strong&gt;FULL OUTER&lt;/strong&gt;.&lt;/li&gt;
&lt;li&gt;Need every combination or a complete reporting grid? &lt;strong&gt;CROSS&lt;/strong&gt;.&lt;/li&gt;
&lt;li&gt;Comparing rows inside one table? &lt;strong&gt;SELF&lt;/strong&gt;.&lt;/li&gt;
&lt;/ul&gt;




&lt;h2&gt;
  
  
  Part 3: Common Table Expressions (CTEs)
&lt;/h2&gt;

&lt;p&gt;A &lt;strong&gt;CTE&lt;/strong&gt; is a named temporary result defined with &lt;code&gt;WITH&lt;/code&gt;, which exists only for the duration of one query. Think of it as giving a sub-step a name so the main query reads top to bottom like a story.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;name&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="p"&gt;...&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="p"&gt;...&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Basic CTE
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Scenario:&lt;/strong&gt; which departments earn more than the average department?&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;dept_revenue&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;department_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;total_revenue&lt;/span&gt;
    &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;
    &lt;span class="k"&gt;group&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;department_id&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_revenue&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;dept_revenue&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;
&lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;department&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt; &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_id&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_revenue&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="k"&gt;avg&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_revenue&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;dept_revenue&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_revenue&lt;/span&gt; &lt;span class="k"&gt;desc&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;department_name&lt;/th&gt;
&lt;th&gt;total_revenue&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Cardiology&lt;/td&gt;
&lt;td&gt;210100&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The four departments bring in KES 488,000 in total, an average of KES 122,000 each, and only Cardiology is above that. The CTE is used twice: once to join names and once inside the average. That is the first advantage of CTEs: &lt;strong&gt;define once, reuse many times.&lt;/strong&gt;&lt;/p&gt;

&lt;h3&gt;
  
  
  Multiple CTEs
&lt;/h3&gt;

&lt;p&gt;Separate several CTEs with commas; later ones can use earlier ones.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Scenario:&lt;/strong&gt; what percentage of total hospital revenue does each department bring in?&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;dept_revenue&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;department_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;total_revenue&lt;/span&gt;
    &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;
    &lt;span class="k"&gt;group&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;department_id&lt;/span&gt;
&lt;span class="p"&gt;),&lt;/span&gt;
&lt;span class="n"&gt;overall&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="k"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_revenue&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;grand_total&lt;/span&gt;
    &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;dept_revenue&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_revenue&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;round&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;100&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_revenue&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="n"&gt;o&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;grand_total&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;pct_of_total&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;dept_revenue&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;
&lt;span class="k"&gt;cross&lt;/span&gt; &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;overall&lt;/span&gt; &lt;span class="n"&gt;o&lt;/span&gt;
&lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;department&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt; &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_id&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_revenue&lt;/span&gt; &lt;span class="k"&gt;desc&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;department_name&lt;/th&gt;
&lt;th&gt;total_revenue&lt;/th&gt;
&lt;th&gt;pct_of_total&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Cardiology&lt;/td&gt;
&lt;td&gt;210100&lt;/td&gt;
&lt;td&gt;43.1&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Orthopedics&lt;/td&gt;
&lt;td&gt;105200&lt;/td&gt;
&lt;td&gt;21.6&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;General Medicine&lt;/td&gt;
&lt;td&gt;97800&lt;/td&gt;
&lt;td&gt;20.0&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Pediatrics&lt;/td&gt;
&lt;td&gt;74900&lt;/td&gt;
&lt;td&gt;15.3&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The second CTE (&lt;code&gt;overall&lt;/code&gt;) is built from the first (&lt;code&gt;dept_revenue&lt;/code&gt;). Cardiology alone brings in 43.1% of revenue. Each step is named and simple, where one giant query would be hard to read.&lt;/p&gt;

&lt;h3&gt;
  
  
  CTEs with joins and aggregations
&lt;/h3&gt;

&lt;p&gt;&lt;strong&gt;Scenario:&lt;/strong&gt; a doctor scorecard with visit count, revenue and average patient satisfaction. Aggregate first in a CTE, then join to the descriptive table.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;doctor_stats&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;doctor_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
           &lt;span class="k"&gt;count&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;                                   &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
           &lt;span class="k"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;                        &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;revenue&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
           &lt;span class="n"&gt;round&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;avg&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;patient_satisfaction_score&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt; &lt;span class="mi"&gt;2&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;  &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;avg_satisfaction&lt;/span&gt;
    &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;
    &lt;span class="k"&gt;group&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;doctor_id&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;doctor_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;doctor_specialty&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;visits&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;revenue&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;avg_satisfaction&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;doctor_stats&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;
&lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;doctors&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt; &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;doctor_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;doctor_id&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;revenue&lt;/span&gt; &lt;span class="k"&gt;desc&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;doctor_name&lt;/th&gt;
&lt;th&gt;doctor_specialty&lt;/th&gt;
&lt;th&gt;visits&lt;/th&gt;
&lt;th&gt;revenue&lt;/th&gt;
&lt;th&gt;avg_satisfaction&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;DR.Brian Mwangi&lt;/td&gt;
&lt;td&gt;Cardiologist&lt;/td&gt;
&lt;td&gt;9&lt;/td&gt;
&lt;td&gt;210100&lt;/td&gt;
&lt;td&gt;4.49&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Dr. Peter Otieno&lt;/td&gt;
&lt;td&gt;Orthopedic Surgeon&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;td&gt;105200&lt;/td&gt;
&lt;td&gt;4.23&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Dr. Sarah Wanjiku&lt;/td&gt;
&lt;td&gt;General Physician&lt;/td&gt;
&lt;td&gt;9&lt;/td&gt;
&lt;td&gt;97800&lt;/td&gt;
&lt;td&gt;4.00&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Dr. Faith Chebet&lt;/td&gt;
&lt;td&gt;Pediatrician&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;td&gt;74900&lt;/td&gt;
&lt;td&gt;4.43&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Aggregating before joining keeps the join small and avoids the classic mistake of aggregating after a join that multiplied rows.&lt;/p&gt;

&lt;h3&gt;
  
  
  Recursive CTEs
&lt;/h3&gt;

&lt;p&gt;A recursive CTE refers to itself. It has two parts joined by &lt;code&gt;UNION ALL&lt;/code&gt;: an &lt;strong&gt;anchor&lt;/strong&gt; (the starting row) and a &lt;strong&gt;recursive member&lt;/strong&gt; that builds on the previous output, repeating until a stopping condition is met. Typical uses are org charts, category trees and, as here, generating a series of dates.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Scenario:&lt;/strong&gt; the visits data covers 1 to 15 June. Which days between 1 and 20 June have no recorded visits? A normal query cannot list days that do not exist in the table, so we generate the calendar first.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="k"&gt;recursive&lt;/span&gt; &lt;span class="n"&gt;days&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="nb"&gt;date&lt;/span&gt; &lt;span class="s1"&gt;'2026-06-01'&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;            &lt;span class="c1"&gt;-- anchor&lt;/span&gt;
    &lt;span class="k"&gt;union&lt;/span&gt; &lt;span class="k"&gt;all&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;days&lt;/span&gt;                   &lt;span class="c1"&gt;-- recursive member&lt;/span&gt;
    &lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt; &lt;span class="nb"&gt;date&lt;/span&gt; &lt;span class="s1"&gt;'2026-06-20'&lt;/span&gt;              &lt;span class="c1"&gt;-- stop condition&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;days&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;count&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;days&lt;/span&gt;
&lt;span class="k"&gt;left&lt;/span&gt; &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt; &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;visit_date&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;days&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;d&lt;/span&gt;
&lt;span class="k"&gt;group&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;days&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;d&lt;/span&gt;
&lt;span class="k"&gt;having&lt;/span&gt; &lt;span class="k"&gt;count&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;days&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;d&lt;/th&gt;
&lt;th&gt;visits&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;2026-06-16&lt;/td&gt;
&lt;td&gt;0&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;2026-06-17&lt;/td&gt;
&lt;td&gt;0&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;2026-06-18&lt;/td&gt;
&lt;td&gt;0&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;2026-06-19&lt;/td&gt;
&lt;td&gt;0&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;2026-06-20&lt;/td&gt;
&lt;td&gt;0&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The CTE produces 20 consecutive dates, the &lt;code&gt;LEFT JOIN&lt;/code&gt; attaches visits, and &lt;code&gt;HAVING&lt;/code&gt; keeps the empty days. Always include a stop condition, otherwise the recursion never ends. (If the hospital stored staff reporting lines, say &lt;code&gt;staff_id&lt;/code&gt; and &lt;code&gt;manager_id&lt;/code&gt;, the same pattern would walk a whole chain of command.)&lt;/p&gt;

&lt;h3&gt;
  
  
  CTEs vs subqueries
&lt;/h3&gt;

&lt;p&gt;Any CTE can be rewritten as a subquery. The same question both ways, 'visits with a bill above the average':&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- Subquery&lt;/span&gt;
&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;total_bill_kes&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;total_bill_kes&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="k"&gt;avg&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;

&lt;span class="c1"&gt;-- CTE&lt;/span&gt;
&lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;avg_bill&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="k"&gt;avg&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;avg_amount&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;avg_bill&lt;/span&gt; &lt;span class="n"&gt;a&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;a&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;avg_amount&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;&lt;/th&gt;
&lt;th&gt;CTE&lt;/th&gt;
&lt;th&gt;Subquery&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Readability&lt;/td&gt;
&lt;td&gt;reads top-down, steps have names&lt;/td&gt;
&lt;td&gt;nested inside the query, harder as depth grows&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Reuse&lt;/td&gt;
&lt;td&gt;can be referenced several times in the same query&lt;/td&gt;
&lt;td&gt;must be repeated each time it is needed&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Recursion&lt;/td&gt;
&lt;td&gt;supported&lt;/td&gt;
&lt;td&gt;not possible&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Scope&lt;/td&gt;
&lt;td&gt;only the one statement that follows&lt;/td&gt;
&lt;td&gt;only where it is written&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Best for&lt;/td&gt;
&lt;td&gt;multi-step logic, reuse, hierarchies&lt;/td&gt;
&lt;td&gt;short, one-off filters or single values&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;In PostgreSQL 12 and later, a CTE that is referenced once is normally inlined, so performance is usually the same as the equivalent subquery. You can force the old behaviour with &lt;code&gt;AS MATERIALIZED&lt;/code&gt;. Rule of thumb: use a subquery for a small one-off filter, and a CTE once the logic has more than one step or you need the result twice.&lt;/p&gt;




&lt;h2&gt;
  
  
  Part 4: SQL Subqueries
&lt;/h2&gt;

&lt;p&gt;A &lt;strong&gt;subquery&lt;/strong&gt; is a &lt;code&gt;SELECT&lt;/code&gt; inside another query, wrapped in parentheses. The inner query supplies a value, a list or a table to the outer query.&lt;/p&gt;

&lt;h2&gt;
  
  
  Where a subquery can sit
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Clause&lt;/th&gt;
&lt;th&gt;What the subquery returns&lt;/th&gt;
&lt;th&gt;Example use&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;WHERE&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;a value or a list used for filtering&lt;/td&gt;
&lt;td&gt;patients who had an inpatient stay&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;SELECT&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;one value per output row (scalar)&lt;/td&gt;
&lt;td&gt;visit count next to each department&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;FROM&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;a table, called a derived table (needs an alias)&lt;/td&gt;
&lt;td&gt;average stay per doctor, then joined&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  Scalar subquery
&lt;/h2&gt;

&lt;p&gt;Returns exactly &lt;strong&gt;one row and one column&lt;/strong&gt;, so it can be used like a value.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Scenario:&lt;/strong&gt; find visits that cost more than the average visit.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;total_bill_kes&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;total_bill_kes&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="k"&gt;avg&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;total_bill_kes&lt;/span&gt; &lt;span class="k"&gt;desc&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The inner query returns one number, KES 16,266.67. Ten visits are above it:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;visit_id&lt;/th&gt;
&lt;th&gt;total_bill_kes&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;1017&lt;/td&gt;
&lt;td&gt;82000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1010&lt;/td&gt;
&lt;td&gt;76000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1004&lt;/td&gt;
&lt;td&gt;42000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1016&lt;/td&gt;
&lt;td&gt;36000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1027&lt;/td&gt;
&lt;td&gt;33000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1026&lt;/td&gt;
&lt;td&gt;29000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1003&lt;/td&gt;
&lt;td&gt;28000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1015&lt;/td&gt;
&lt;td&gt;24000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1030&lt;/td&gt;
&lt;td&gt;22000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1024&lt;/td&gt;
&lt;td&gt;18500&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;If a scalar subquery returns more than one row, PostgreSQL raises an error.&lt;/p&gt;

&lt;h3&gt;
  
  
  Non-correlated subquery
&lt;/h3&gt;

&lt;p&gt;The inner query is &lt;strong&gt;independent&lt;/strong&gt; of the outer query: it runs once, and its result is handed to the outer query. The scalar example above is non-correlated, and so is the &lt;code&gt;IN&lt;/code&gt; example below.&lt;/p&gt;

&lt;h3&gt;
  
  
  Correlated subquery
&lt;/h3&gt;

&lt;p&gt;The inner query &lt;strong&gt;refers to a column of the outer query&lt;/strong&gt;, so it is re-evaluated for every outer row.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Scenario:&lt;/strong&gt; find visits that cost more than the average &lt;strong&gt;for their own department&lt;/strong&gt;, a fairer comparison than the global average.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="k"&gt;avg&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;v2&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt; &lt;span class="n"&gt;v2&lt;/span&gt;
    &lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;v2&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_id&lt;/span&gt;   &lt;span class="c1"&gt;-- link to the outer row&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;visit_id&lt;/th&gt;
&lt;th&gt;department_id&lt;/th&gt;
&lt;th&gt;total_bill_kes&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;1010&lt;/td&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;td&gt;76000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1017&lt;/td&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;td&gt;82000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1009&lt;/td&gt;
&lt;td&gt;20&lt;/td&gt;
&lt;td&gt;15000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1016&lt;/td&gt;
&lt;td&gt;20&lt;/td&gt;
&lt;td&gt;36000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1026&lt;/td&gt;
&lt;td&gt;20&lt;/td&gt;
&lt;td&gt;29000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1003&lt;/td&gt;
&lt;td&gt;30&lt;/td&gt;
&lt;td&gt;28000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1015&lt;/td&gt;
&lt;td&gt;30&lt;/td&gt;
&lt;td&gt;24000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1027&lt;/td&gt;
&lt;td&gt;30&lt;/td&gt;
&lt;td&gt;33000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1004&lt;/td&gt;
&lt;td&gt;40&lt;/td&gt;
&lt;td&gt;42000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;1024&lt;/td&gt;
&lt;td&gt;40&lt;/td&gt;
&lt;td&gt;18500&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Visit 1009 (KES 15,000) is below the hospital average of 16,267, so it was missing from the previous list, but it is well above its department's average of about 10,867, so it appears here. That is the value of correlation. The same result can be achieved with &lt;code&gt;AVG() OVER (PARTITION BY department_id)&lt;/code&gt;, which is often faster on large tables.&lt;/p&gt;

&lt;h3&gt;
  
  
  IN
&lt;/h3&gt;

&lt;p&gt;&lt;code&gt;IN&lt;/code&gt; tests whether a value appears in the list the subquery returns.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Scenario:&lt;/strong&gt; names of patients who have been admitted as inpatients.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;patient_name&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;patients&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;patient_id&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;patient_id&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt; &lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;admission_type&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'Inpatient'&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;patient_id&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Returns 9 patients: Grace Naliaka, David Mutua, Peter Ouma, Eunice Moraa, Francis Njuguna, Hellen Jepchirchir, Nadia Hussein, Patrick Muli and Ruth Nekesa.&lt;/p&gt;

&lt;p&gt;&lt;code&gt;NOT IN&lt;/code&gt; finds the opposite, but beware: if the subquery returns even one &lt;code&gt;NULL&lt;/code&gt;, &lt;code&gt;NOT IN&lt;/code&gt; returns &lt;strong&gt;no rows at all&lt;/strong&gt;. Prefer &lt;code&gt;NOT EXISTS&lt;/code&gt; when the column can be &lt;code&gt;NULL&lt;/code&gt;. In &lt;code&gt;visits&lt;/code&gt;, columns such as &lt;code&gt;ward_id&lt;/code&gt;, &lt;code&gt;diagnosis_id&lt;/code&gt; and &lt;code&gt;procedure_id&lt;/code&gt; are nullable.&lt;/p&gt;

&lt;h3&gt;
  
  
  EXISTS
&lt;/h3&gt;

&lt;p&gt;&lt;code&gt;EXISTS&lt;/code&gt; returns true if the subquery returns &lt;strong&gt;at least one row&lt;/strong&gt;. It is normally written as a correlated subquery, and it stops searching at the first match.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Scenario:&lt;/strong&gt; patients who have ever been triaged as Red (the most urgent level).&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_name&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;patients&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="k"&gt;exists&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
    &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;
    &lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt;
      &lt;span class="k"&gt;and&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;triage_level&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'Red'&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Returns 6 patients: Grace Naliaka, David Mutua, Peter Ouma, Eunice Moraa, Hellen Jepchirchir and Ruth Nekesa. The &lt;code&gt;select 1&lt;/code&gt; is a convention: &lt;code&gt;EXISTS&lt;/code&gt; only cares whether a row exists, not what it contains.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;IN vs EXISTS:&lt;/strong&gt; both answer 'is there a match?'. &lt;code&gt;IN&lt;/code&gt; compares against a list; &lt;code&gt;EXISTS&lt;/code&gt; checks for existence and is safer with &lt;code&gt;NULL&lt;/code&gt;s (especially as &lt;code&gt;NOT EXISTS&lt;/code&gt;). On modern PostgreSQL the planner often treats them the same for simple cases.&lt;/p&gt;

&lt;h3&gt;
  
  
  ANY and ALL
&lt;/h3&gt;

&lt;p&gt;These compare one value against &lt;strong&gt;every&lt;/strong&gt; value the subquery returns.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;code&gt;&amp;gt; ANY (subquery)&lt;/code&gt;: greater than &lt;strong&gt;at least one&lt;/strong&gt; returned value (so, greater than the minimum).&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;&amp;gt; ALL (subquery)&lt;/code&gt;: greater than &lt;strong&gt;every&lt;/strong&gt; returned value (so, greater than the maximum).&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;= ANY&lt;/code&gt; is equivalent to &lt;code&gt;IN&lt;/code&gt;.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Scenario:&lt;/strong&gt; compare Pediatrics visits (department 40) with General Medicine visits (department 20).&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- Pediatrics bills higher than AT LEAST ONE General Medicine bill&lt;/span&gt;
&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;total_bill_kes&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;department_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;40&lt;/span&gt;
  &lt;span class="k"&gt;and&lt;/span&gt; &lt;span class="n"&gt;total_bill_kes&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="k"&gt;any&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;total_bill_kes&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt; &lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;department_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;20&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;

&lt;span class="c1"&gt;-- Pediatrics bills higher than EVERY General Medicine bill&lt;/span&gt;
&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;total_bill_kes&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;department_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;40&lt;/span&gt;
  &lt;span class="k"&gt;and&lt;/span&gt; &lt;span class="n"&gt;total_bill_kes&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="k"&gt;all&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;total_bill_kes&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt; &lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;department_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;20&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;General Medicine bills range from KES 2,000 to 36,000. So:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;code&gt;ANY&lt;/code&gt; keeps Pediatrics bills above 2,000: visits 1004 (42,000), 1007 (5,500), 1019 (5,200) and 1024 (18,500).&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;ALL&lt;/code&gt; keeps only bills above 36,000: just visit 1004 (42,000).&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Nested subqueries
&lt;/h2&gt;

&lt;p&gt;Subqueries can sit inside other subqueries. Read them from the inside out.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Scenario:&lt;/strong&gt; which patients were treated by the more experienced doctors, meaning those with more years of experience than the average doctor?&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_name&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;patients&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;patient_id&lt;/span&gt;
    &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;
    &lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;doctor_id&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
        &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;doctor_id&lt;/span&gt;
        &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;doctors&lt;/span&gt;
        &lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;doctor_experience_years&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="k"&gt;avg&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;doctor_experience_years&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;doctors&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Step by step: the innermost query returns the average experience (11 years); the next level returns the doctors above it (Dr. Brian Mwangi with 12 and Dr. Peter Otieno with 15); the next returns the patients who visited either of them; and the outer query turns those IDs into 12 names (Amina Otieno, John Kariuki, Grace Naliaka, Esther Achieng, Samuel Kiptoo, Peter Ouma, Catherine Musyoka, Eunice Moraa, Hellen Jepchirchir, Kevin Barasa, Moses Cheruiyot and Ruth Nekesa). Three levels works, but the same logic written as CTEs or joins would be easier to read, which is why deep nesting is usually a sign to switch.&lt;/p&gt;

&lt;h3&gt;
  
  
  Subquery in SELECT
&lt;/h3&gt;

&lt;p&gt;A scalar subquery in the &lt;code&gt;SELECT&lt;/code&gt; list adds a computed column to every row.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="k"&gt;count&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt; &lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;visit_count&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="k"&gt;max&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt; &lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;highest_bill&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;department&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_id&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;department_name&lt;/th&gt;
&lt;th&gt;visit_count&lt;/th&gt;
&lt;th&gt;highest_bill&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Cardiology&lt;/td&gt;
&lt;td&gt;9&lt;/td&gt;
&lt;td&gt;82000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;General Medicine&lt;/td&gt;
&lt;td&gt;9&lt;/td&gt;
&lt;td&gt;36000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Orthopedics&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;td&gt;33000&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Pediatrics&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;td&gt;42000&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Each subquery is correlated and runs once per department, which is fine for four rows but would be slow for large outer tables; a join with &lt;code&gt;GROUP BY&lt;/code&gt; scales better.&lt;/p&gt;

&lt;h3&gt;
  
  
  Subquery in FROM (derived table)
&lt;/h3&gt;

&lt;p&gt;A subquery in &lt;code&gt;FROM&lt;/code&gt; acts as a temporary table. It &lt;strong&gt;must&lt;/strong&gt; have an alias.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Scenario:&lt;/strong&gt; average length of stay per doctor, joined back to the doctor names.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;doctor_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;t&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;avg_stay_days&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;doctor_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;round&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;avg&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;length_of_stay_days&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt; &lt;span class="mi"&gt;2&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;avg_stay_days&lt;/span&gt;
    &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt;
    &lt;span class="k"&gt;group&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;doctor_id&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;t&lt;/span&gt;
&lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;doctors&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt; &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;doctor_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;t&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;doctor_id&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;t&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;avg_stay_days&lt;/span&gt; &lt;span class="k"&gt;desc&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;doctor_name&lt;/th&gt;
&lt;th&gt;avg_stay_days&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;DR.Brian Mwangi&lt;/td&gt;
&lt;td&gt;1.33&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Dr. Peter Otieno&lt;/td&gt;
&lt;td&gt;1.00&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Dr. Faith Chebet&lt;/td&gt;
&lt;td&gt;1.00&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Dr. Sarah Wanjiku&lt;/td&gt;
&lt;td&gt;0.56&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The average includes outpatient visits (which have a stay of 0 days), so the numbers reflect the doctor's whole workload, not just admissions. Dr. Mwangi's cardiac cases keep patients longest.&lt;/p&gt;




&lt;h2&gt;
  
  
  Putting it all together
&lt;/h2&gt;

&lt;p&gt;The four tools are not competitors; they are used together. A single report can use a CTE to aggregate, joins to attach names, a window function to rank and a subquery to filter. For example: 'for each department, show the top-billing visit with its patient and doctor':&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;ranked&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;visit_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;doctor_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
           &lt;span class="n"&gt;row_number&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;partition&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_id&lt;/span&gt; &lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt; &lt;span class="k"&gt;desc&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;rn&lt;/span&gt;
    &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;visits&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;dep&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;doctor_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;ranked&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;
&lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;department&lt;/span&gt; &lt;span class="n"&gt;dep&lt;/span&gt; &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;dep&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;department_id&lt;/span&gt;
&lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;patients&lt;/span&gt;   &lt;span class="n"&gt;p&lt;/span&gt;   &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt;      &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;patient_id&lt;/span&gt;
&lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;doctors&lt;/span&gt;    &lt;span class="n"&gt;d&lt;/span&gt;   &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;d&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;doctor_id&lt;/span&gt;       &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;doctor_id&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;rn&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_bill_kes&lt;/span&gt; &lt;span class="k"&gt;desc&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;This gives one row per department: Cardiology's top bill is KES 82,000 (Hellen Jepchirchir, Dr. Brian Mwangi), Pediatrics' is 42,000 (David Mutua, Dr. Faith Chebet), General Medicine's is 36,000 (Francis Njuguna, Dr. Sarah Wanjiku) and Orthopedics' is 33,000 (Ruth Nekesa, Dr. Peter Otieno).&lt;/p&gt;

&lt;h2&gt;
  
  
  Quick reference
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Need&lt;/th&gt;
&lt;th&gt;Tool&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Rank rows, with or without gaps&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;RANK&lt;/code&gt;, &lt;code&gt;DENSE_RANK&lt;/code&gt;, &lt;code&gt;ROW_NUMBER&lt;/code&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Compare with previous or next row&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;LAG&lt;/code&gt;, &lt;code&gt;LEAD&lt;/code&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Running total or moving average&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;SUM&lt;/code&gt;/&lt;code&gt;AVG&lt;/code&gt; with &lt;code&gt;OVER (ORDER BY ... ROWS ...)&lt;/code&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Combine tables&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;INNER&lt;/code&gt;, &lt;code&gt;LEFT&lt;/code&gt;, &lt;code&gt;FULL&lt;/code&gt;, &lt;code&gt;CROSS&lt;/code&gt;, self joins&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Break a problem into named steps&lt;/td&gt;
&lt;td&gt;CTE&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Walk a hierarchy or generate a series&lt;/td&gt;
&lt;td&gt;recursive CTE&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Filter by a value or list from another query&lt;/td&gt;
&lt;td&gt;subquery with &lt;code&gt;IN&lt;/code&gt;, &lt;code&gt;EXISTS&lt;/code&gt;, &lt;code&gt;ANY&lt;/code&gt;, &lt;code&gt;ALL&lt;/code&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  Conclusion
&lt;/h2&gt;

&lt;p&gt;Window functions let you analyse rows in context without losing detail; joins connect the dimension tables to the visits fact table; CTEs make multi-step logic readable; and subqueries give you precise, flexible filters. On the hospital dataset these tools answered real questions: which patients stayed longest, which departments carry the revenue (Cardiology, with 43.1%), which returning patients' bills rose or fell, and who has never been admitted. The best way to build the skill is to write each query yourself, change a condition and watch how the result changes. The goal is not to memorise syntax but to recognise the shape of a question and pick the right tool.&lt;/p&gt;

</description>
      <category>database</category>
      <category>datascience</category>
      <category>sql</category>
    </item>
    <item>
      <title>Data Modelling, Relationship and Joins</title>
      <dc:creator>Adrian Mageto</dc:creator>
      <pubDate>Wed, 16 Sep 2026 05:23:25 +0000</pubDate>
      <link>https://dev.to/adrian_mageto/data-modelling-relationship-and-joins-1a12</link>
      <guid>https://dev.to/adrian_mageto/data-modelling-relationship-and-joins-1a12</guid>
      <description>&lt;h1&gt;
  
  
  Introduction
&lt;/h1&gt;

&lt;p&gt;Data modelling is the process of organizing, structuring and defining relationships between data tables to enable meaningful analysis. Think of it as designing the blueprint of a building without solid foundation, everything on top will be unstable.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why Data Modelling Matters for Your Power BI Reports
&lt;/h2&gt;

&lt;p&gt;Performance: Well-modelled data leads to faster report refresh times and responsive visuals. Reports that take 5minutes can be reduced to 30 seconds with proper modelling. &lt;/p&gt;

&lt;p&gt;Accuracy: Proper relationships ensure your calculations produce correct results, eliminating discrepancies between reports and source data.&lt;/p&gt;

&lt;p&gt;Scalability: Good models can grow with you business without requiring complete rebuilds, saving time and resources.&lt;/p&gt;

&lt;p&gt;Maintainability: Clean models are easier to update, troubleshoot and handoff to colleagues, reducing technical debt.&lt;/p&gt;

&lt;p&gt;Simple Reports: A  good model reduces the need for complicated formulas and makes visualizations easier to create.&lt;/p&gt;

&lt;h2&gt;
  
  
  Data Modelling in Power BI
&lt;/h2&gt;

&lt;p&gt;In power BI, data modelling involves:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Ensuring data accuracy and consistency.&lt;/li&gt;
&lt;li&gt;Creating relationships between tables.&lt;/li&gt;
&lt;li&gt;Optimizing the structure of data for performance.&lt;/li&gt;
&lt;li&gt;Defining calculated columns and measures.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  1: Flat Table
&lt;/h2&gt;

&lt;p&gt;Single table where all the data needed for analysis is stored together rather than being separated to fact and dimension table.&lt;/p&gt;

&lt;p&gt;Example:&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fco5v9hjzpl1p82lebkdy.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fco5v9hjzpl1p82lebkdy.png" alt="Flat Table" width="800" height="73"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h3&gt;
  
  
  Advantages
&lt;/h3&gt;

&lt;ul&gt;
&lt;li&gt;Simple to create and understand&lt;/li&gt;
&lt;li&gt;Easy to use for small datasets.&lt;/li&gt;
&lt;li&gt;Requires fewer relationships between tables&lt;/li&gt;
&lt;li&gt;Useful for quick analysis&lt;/li&gt;
&lt;/ul&gt;

&lt;h3&gt;
  
  
  Disadvantages
&lt;/h3&gt;

&lt;ul&gt;
&lt;li&gt;DAX measures become complex and error-prone&lt;/li&gt;
&lt;li&gt;Duplicate data everywhere&lt;/li&gt;
&lt;li&gt;Slow report performance as data grow&lt;/li&gt;
&lt;li&gt;Hard to maintain when source data changes&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Star schema
&lt;/h2&gt;

&lt;p&gt;A star schema organizes data into one central fact table surrounded by several dimension tables, each directly connected to the fact table by a single relationship.&lt;/p&gt;

&lt;h3&gt;
  
  
  Advantages
&lt;/h3&gt;

&lt;ul&gt;
&lt;li&gt;&lt;p&gt;Simplicity: Easy to understand and navigate for both technical and business users.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Performance: Fewer joins mean faster queries and report loading times.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;DAX-Friendly: Calculations are straightforward and intuitive.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Scalability: Easy to add new dimensions without restructuring.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;User Experience: Business users can navigate relationships naturally.&lt;/p&gt;&lt;/li&gt;
&lt;/ul&gt;

&lt;h3&gt;
  
  
  Disadvantages
&lt;/h3&gt;

&lt;ul&gt;
&lt;li&gt;&lt;p&gt;More tables: Data is split into fact and dimension tables, so the model can be more complex than a single flat table&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Requires relationship: You need to correctly create and manage relationships between the fact and dimension tables.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Requires proper planning: You need to understand things like granularity, keys, cardinality and filter direction to build the model correctly&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Can be confusing to beginners: Someone new to Power BI may initially find multiple related tables harder to understand than one flat table.&lt;/p&gt;&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Example of a star schema:&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F7h97yvxhxpkdsi48cg6o.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F7h97yvxhxpkdsi48cg6o.png" alt="Star schema" width="799" height="625"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;The "CustomerID" column contains unique values in the DimCustomer table because each customer is represented only once. In this table, "CustomerID" acts as the primary key, uniquely identifying each customer.&lt;/p&gt;

&lt;p&gt;However, the same "CustomerID" can appear multiple times in the FactSales table because the fact table records individual sales transactions. A single customer may make multiple purchases, meaning their "CustomerID" will be repeated for each transaction.&lt;/p&gt;

&lt;p&gt;For example, if customer C001 makes three purchases, C001 will appear once in the "DimCustomer" table but three times in the "FactSales" table. This creates a one-to-many (1:*) relationship, where one customer in the dimension table can be associated with many sales transactions in the fact table.&lt;/p&gt;

&lt;p&gt;This structure allows Power BI to connect customer information with their transactions and perform analysis such as total sales per customer, number of purchases, or average spending.&lt;/p&gt;

&lt;h2&gt;
  
  
  Snowflake Schema
&lt;/h2&gt;

&lt;p&gt;The snowflake schema normalises dimension tables into sub-dimensions, creating a more complex structure. &lt;/p&gt;

&lt;p&gt;STRUCTURE:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Central fact table.&lt;/li&gt;
&lt;li&gt;Dimension table broken into normalised sub-tables&lt;/li&gt;
&lt;li&gt;More relationships and joins required &lt;/li&gt;
&lt;li&gt;Reduced data redundancy&lt;/li&gt;
&lt;/ul&gt;

&lt;h3&gt;
  
  
  Example of a snowflake schema
&lt;/h3&gt;

&lt;p&gt;Below is a simplified representation of how a snowflake schema model looks&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fxjiqw6zopuw6tp0km0de.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fxjiqw6zopuw6tp0km0de.png" alt="Snowflake schema" width="800" height="411"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Difference between a Snowflake and a Star schema
&lt;/h2&gt;

&lt;p&gt;The difference is shown in the image below:&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fndst3l9rz2kdzgtd77o5.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fndst3l9rz2kdzgtd77o5.png" alt="Difference " width="799" height="572"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Advantages
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Better for certain data warehousing scenarios&lt;/li&gt;
&lt;li&gt;Improves data integrity through normalization &lt;/li&gt;
&lt;li&gt;Supports detailed hierarchical drill-down&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Disadvantages
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Slower query performance due to additional joins&lt;/li&gt;
&lt;li&gt;More complex DAX calculations required &lt;/li&gt;
&lt;li&gt;Harder for business users to navigate and understand &lt;/li&gt;
&lt;li&gt;More complex relationships to manage &lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Fact Tables and Dimension Tables
&lt;/h2&gt;

&lt;p&gt;Every star (or snowflake) schema is built from two kinds of tables that play very different roles.&lt;/p&gt;

&lt;h3&gt;
  
  
  Fact Tables
&lt;/h3&gt;

&lt;p&gt;A fact table stores the measurable, numeric business events for report analysis. It usually contains metrics, measurements or business operation facts in addition to foreign keys.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;Typically stored in a fact table&lt;/strong&gt;: foreign keys to related dimensions, numeric measures (Sales Amount, Quantity, Cost), and sometimes a degenerate identifier such as as a transaction number.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;Grain/ granularity&lt;/strong&gt;: The grain of a fact table is the level of detail one row represents, for example, "one row per sales order line" versus "one row per order."&lt;br&gt;
Choosing the grain correctly up front is critical: too coarse a grain loses detail needed for analysis, while too fine a grain increases row counts and file size unnecessarily. All measures in a fact table should be defined at a single, consistent grain.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Common examples: FactSales, FactOrders, Fact Transactions, FactInventory&lt;/p&gt;&lt;/li&gt;
&lt;/ul&gt;

&lt;h3&gt;
  
  
  Dimension Tables
&lt;/h3&gt;

&lt;p&gt;A dimension table is a database table that stores attributes  describing the facts in a fact table.&lt;/p&gt;

&lt;p&gt;The dimension plays an important role in its model because they provide reference information about a set of measurable events, found in the fact table.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;**  What is typically stored in a dimension table** : a unique key column, descriptive text attributes (names, categories, regions), and sometimes hierarchies &lt;/li&gt;
&lt;li&gt;Common examples: DimCustomer, Dim product, DimDate, DimLocation.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Measures vs Descriptive Attributes
&lt;/h2&gt;

&lt;p&gt;Measures are quantitative values that can be aggregated-the raw numbers in your data that you want to analyze.&lt;/p&gt;

&lt;p&gt;Example of measures:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;p&gt;Sales amount ($)&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Quantity sold (units)&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Revenue&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Transaction Count&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Page views&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Click count&lt;/p&gt;&lt;/li&gt;
&lt;/ul&gt;

&lt;h3&gt;
  
  
  Attributes
&lt;/h3&gt;

&lt;p&gt;Are descriptive characteristics or properties of data entities. They provide additional information about the data but aren't typically aggregated.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Examples:&lt;/strong&gt;&lt;br&gt;
Customer attributes:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Name&lt;/li&gt;
&lt;li&gt;Email&lt;/li&gt;
&lt;li&gt;Phone number&lt;/li&gt;
&lt;li&gt;Address&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Product Attributes:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Color&lt;/li&gt;
&lt;li&gt;Size &lt;/li&gt;
&lt;li&gt;Brand 
-Category&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Below is a practical example of of FactSales and its Dimension:&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F12w1i43ho4bkqc7oztq8.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F12w1i43ho4bkqc7oztq8.png" alt="FactSales and its Dimension" width="800" height="406"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Relationship in Power BI
&lt;/h2&gt;

&lt;p&gt;Power BI allow users to create data models by establishing relationships between related tables.&lt;/p&gt;

&lt;p&gt;These relationships connect tables through common columns and allow filter and calculations to work across the model. Power BI can automatically detect some relationship, while others can be created or modified manually.&lt;/p&gt;

&lt;h2&gt;
  
  
  Type of Table Relationship
&lt;/h2&gt;

&lt;h3&gt;
  
  
  1: One-to-Many (1:*)
&lt;/h3&gt;

&lt;p&gt;A value in one table can be related to multiple rows in another table. This is one of the most common relationship types in PowerBI.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F52n8aqcprbkqiiv28elb.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F52n8aqcprbkqiiv28elb.png" alt="One-to-many relationship" width="756" height="208"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Example:&lt;/strong&gt; One row in DimCustomer(CustomerID =1000) relates to many rows in FactSales&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;when not to use:&lt;/strong&gt; Avoid forcing a one to many relationship between  two tables that are actually both at a "many" grain - that scenario calls for a many-to-many relationship or a bridge table instead.&lt;/p&gt;

&lt;h3&gt;
  
  
  2.One to One (1:1)
&lt;/h3&gt;

&lt;p&gt;Is a relationship setting that describes how two tables are related to each other. To put it simply, it means that for each unique value in one table, there is exactly one corresponding unique value in the other table, and vice versa.&lt;/p&gt;

&lt;p&gt;Imagine you have two tables: One containing a list of students and their Students IDs, and another containing a list of courses and their courses IDs. If you establish a one-to-one cardinality relationship between these tables based on the student ID and Course ID, it means each student is enrolled  in one and only one course, and each course is taken by one and only one student.&lt;/p&gt;

&lt;p&gt;If both tables could simply be combined into one without redundancy, a one-to-one relationship is usually unnecessary complexity, merging them is often cleaner.&lt;/p&gt;

&lt;h3&gt;
  
  
  3: Many-to-Many (&lt;em&gt;:&lt;/em&gt;) Relationship
&lt;/h3&gt;

&lt;p&gt;Refers to a relationship between two tables where multiple values in one table can be associated with multiple values in another table.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Example:&lt;/strong&gt;
Imagine you have two tables in your Power BI project: One for "students" and another for "courses." If a student can enroll in multiple courses, and a course can have multiple students, you have a "Many-to-Many" relationship.
In this scenario, each student is associated with multiple courses, and each course is associated with many students.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;When not to use:&lt;/strong&gt;&lt;br&gt;
Many-to-Many relationship should not be used as a quick fix for poor key design; can produce double-counted aggregation if not modelled carefully, and a proper bridge table is usually a safer alternative.&lt;/p&gt;

&lt;h2&gt;
  
  
  Keys, Cardinality and Integrity
&lt;/h2&gt;

&lt;h3&gt;
  
  
  Keys
&lt;/h3&gt;

&lt;p&gt;Relational databases form the backbone of modern data management, ensuring data integrity and seamless relationships between datasets.&lt;br&gt;
Two critical concepts that make this possible are &lt;strong&gt;Primary Keys&lt;/strong&gt; and &lt;strong&gt;Foreign Keys&lt;/strong&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  Primary Key
&lt;/h2&gt;

&lt;p&gt;A primary key is a column (or a set of columns) in a table that uniquely identifies each row in that table . It ensures that no two rows have the same identifier, and it cannot contain null values.&lt;/p&gt;

&lt;h3&gt;
  
  
  Key characteristics of Primary Key:
&lt;/h3&gt;

&lt;ul&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;Uniqueness:&lt;/strong&gt; Ensures no duplicate values&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;Non-nullability:&lt;/strong&gt; Guarantees that every row has a valid identifier.&lt;/p&gt;&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Foreign Key
&lt;/h2&gt;

&lt;p&gt;A Foreign Key is a column (set of columns) in one table that references the Primary Key of another table. It establishes a relationship between the two tables and enforces referential integrity, ensuring that the data in the foreign key column matches data in the referenced primary key column.&lt;/p&gt;

&lt;h3&gt;
  
  
  Key Characteristics of Foreign Key
&lt;/h3&gt;

&lt;ul&gt;
&lt;li&gt;&lt;p&gt;References a Primary Key in another table.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Ensures relationships between data sets.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Prevents invalid entries by enforcing constraints.&lt;/p&gt;&lt;/li&gt;
&lt;/ul&gt;

&lt;h3&gt;
  
  
  Why Do We Need Primary Keys and Foreign Keys
&lt;/h3&gt;

&lt;p&gt;&lt;strong&gt;1. Data Integrity&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;p&gt;A primary Key ensures that every record is unique and identifiable.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Foreign Keys ensures that relationships between tables are valid, preventing orphaned records or invalid references.&lt;/p&gt;&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;2. Avoid Data Redundancy&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;By splitting data into multiple tables and using keys to connect them, we avoid duplicating data unnecessarily.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;3. Ease of Querying Data&lt;/strong&gt;&lt;br&gt;
Keys make it easy to write queries that join tables and retrieve related data efficiently.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;4. Enforcing Business Rules&lt;/strong&gt;&lt;br&gt;
Foreign keys enforce rules such as "a student cannot enroll in a course unless they exist in a student's table"&lt;/p&gt;

&lt;h2&gt;
  
  
  Unique Values
&lt;/h2&gt;

&lt;p&gt;a dimension's key column should contain unique values so that Power BI can use it as the “one” side of a one-to-many relationship; this is precisely why CustomerID appears once per customer in DimCustomer but many times in FactSales — DimCustomer stores one description of each customer, while FactSales logs every transaction that customer generated.&lt;/p&gt;

&lt;h2&gt;
  
  
  Cardinality
&lt;/h2&gt;

&lt;p&gt;Cardinality specifies how the rows in one table are related to the rows in another table. &lt;br&gt;
Cardinality is a crucial concept in data modelling because it helps define how data from different tables can be combined and used in visualizations  and calculations.&lt;/p&gt;

&lt;p&gt;Imagine that you have a big box with only red cups and blue cups, you have a cardinality of two because there are two different kinds. But if you have a box with red, blue green and yellow cups, you have a cardinality of four because there are of different kinds.&lt;/p&gt;

&lt;p&gt;In Power BI, cardinality is important because it helps you understand how diverse your data is. It tells you how many unique values there are in a column or a set of data. This information is useful for creating charts, graphs and reports to see patterns and make decisions.&lt;/p&gt;

&lt;h2&gt;
  
  
  Integrity
&lt;/h2&gt;

&lt;h3&gt;
  
  
  Referential Integrity
&lt;/h3&gt;

&lt;p&gt;Is a rule that ensures that the relationships between tables in a relational database remain valid and consistent. It guarantees that a foreign key value always point to an existing valid record inn another table, thus preventing orphaned records or broken links within the data.&lt;/p&gt;

&lt;p&gt;Referential integrity ensures that every foreign key value has a corresponding primary key value in the related table.&lt;br&gt;
For example, if a foreign key in a table references a Customer ID in a customer table, referential integrity ensures that the customer ID exists.&lt;/p&gt;

&lt;h3&gt;
  
  
  Why Referential Integrity Matters
&lt;/h3&gt;

&lt;p&gt;Inconsistent or invalid relationships between tables can lead to various issues in data analysis, reporting, and application functionality. Without referential integrity:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;p&gt;Orphaned record may exist, leading to in accurate data insights.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Applications that rely on relational  data can encounter errors or unexpected behavior.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Data quality deteriorates, making it harder to maintain trust in the system.&lt;/p&gt;&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Active and Inactive Relationships
&lt;/h2&gt;

&lt;h3&gt;
  
  
  Active relationship
&lt;/h3&gt;

&lt;p&gt;The active relationship is the primary relationship that is used by Power BI to calculate and filter data automatically.&lt;/p&gt;

&lt;h3&gt;
  
  
  Inactive Relationship
&lt;/h3&gt;

&lt;p&gt;An inactive relationship is a secondary relationship activated with the Power BI DAX function temporarily whenever there is a need to perform a specific calculation.&lt;/p&gt;

&lt;h2&gt;
  
  
  Filter Direction
&lt;/h2&gt;

&lt;p&gt;It refers to the way filters propagate between related tables in your power BI data model.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F0fbmd0oqwqwd4251osmq.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F0fbmd0oqwqwd4251osmq.png" alt="Demonstration on selecting a value from a dimension can filter records in FactSale" width="800" height="304"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;Single Direction&lt;/strong&gt;&lt;br&gt;
Filter flow in one direction, from one table to another.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;Both Direction (Bi-Directional)&lt;/strong&gt;&lt;br&gt;
Filter flow in both directions between the two tables.&lt;/p&gt;&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Why Filter Direction Matters
&lt;/h2&gt;

&lt;p&gt;It impacts how your visuals and calculations behave in Power BI. It determines:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;Data Filtering:&lt;/strong&gt;&lt;br&gt;
Which rows are included or excluded in your reports based on related data .&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;Performance:&lt;/strong&gt;&lt;br&gt;
Single-Direction filters are generally more efficient, while bi-directional filters can increase complexity and computational load.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;Cross-Filtering relationships:&lt;/strong&gt;&lt;br&gt;
Whether interactions between tables are undirectional or bidirectional.&lt;/p&gt;&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;&lt;strong&gt;When to use Bi-Directional Filtering&lt;/strong&gt;&lt;br&gt;
Useful in scenarios like:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;p&gt;Many-to-Many relationship&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Creating detailed reporting where multiple table need simultaneous filtering.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Solving  specific modelling challenges where single-direction filters don't suffice.&lt;/p&gt;&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Key Takeaways&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;p&gt;Use &lt;strong&gt;Single-Direction filters&lt;/strong&gt; by default for simplicity and performance.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Apply &lt;strong&gt;bi-directional filters&lt;/strong&gt; only when necessary  and with caution.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Always test your model to ensure filter directions work as intended without causing errors or performance degradation.&lt;/p&gt;&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Joins in Power Query
&lt;/h2&gt;

&lt;p&gt;Power query is a key tool for Power BI users. It help transform and combine data from different sources. With its simple features, it facilitates  cleaning, formatting, and restructuring data before intergration into Power BI.&lt;/p&gt;

&lt;p&gt;Aa major function of the power query is the ability to perform joins between tables.&lt;br&gt;
Joins are crucial for uniting data from various sources. They allow you to create coherent and useful data for your analyses.&lt;/p&gt;

&lt;h3&gt;
  
  
  Different Types of Joins in Power Query
&lt;/h3&gt;

&lt;p&gt;Power Query allows combining data from different sources. There are several types of joins. Each type has its own uses.&lt;/p&gt;

&lt;p&gt;1.&lt;strong&gt;Left Outer Join&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;The left outer join includes all record from the left table. Records without a match have null values for the right table.&lt;/p&gt;

&lt;p&gt;2.&lt;strong&gt;Right Outer Join&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;The right outer join includes all records from the right table. Records without a match have null values for the left table.&lt;/p&gt;

&lt;p&gt;3.&lt;strong&gt;Full Outer Join&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;The full outer joins combines the results of left and right outer joins. It includes all records from both tables. Records without a match have null values.&lt;/p&gt;

&lt;p&gt;4.&lt;strong&gt;Inner Join&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;The inner join is widely used. It combines records from two tables by a common key&lt;br&gt;
Only matching row included.&lt;/p&gt;

&lt;p&gt;5.&lt;strong&gt;Left Anti Join&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;It returns only the contents that are contained in the lef table.&lt;/p&gt;

&lt;p&gt;6.&lt;strong&gt;Right Anti Join&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;It returns only the contents that are contained in the right table.&lt;/p&gt;

&lt;h2&gt;
  
  
  Power Query Joins vs Power BI Relationship
&lt;/h2&gt;

&lt;p&gt;Merging in Power Query and creating a relationship in the data model solve similar-sounding problems — combining information from two tables — but they work at different stages of the Power BI workflow and have very different consequences for the model.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F1947s82in7z1nl3v4usq.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F1947s82in7z1nl3v4usq.png" alt=". A Power Query merge physically combines columns into one table; a model relationship keeps tables separate and links them logically." width="800" height="389"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F4jjx03xufcx9cv6fndt7.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F4jjx03xufcx9cv6fndt7.png" alt="A simplified description of joins and power query" width="800" height="298"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Excessive merging pushes a model back toward the flat-table pattern discussed in Section 1: every merge duplicates columns from one table across another, increasing redundancy and file size while making the model harder to maintain (a change to a customer's name would need to be re-merged everywhere it was copied). Keeping fact and dimension tables separate and joined only by relationships preserves the storage efficiency of the star schema, keeps each table focused on a single business concept, and lets Power BI's engine optimise filtering between them — something it cannot do once the data has already been flattened into one table by a merge.&lt;/p&gt;

&lt;p&gt;In short: use a merge in Power Query only when a query genuinely needs columns from two sources combined at the row level (for example, enriching a staging table before it is split into proper fact and dimension tables); use a relationship in the model for everything else.&lt;/p&gt;

&lt;h2&gt;
  
  
  Recommended Power BI Model
&lt;/h2&gt;

&lt;p&gt;For a typical business intelligence project, a star schema, connected by one-to-many relationships filtering in a single direction from dimensions to the fact table, is the recommended design.&lt;br&gt;
A good Power BI model often follows the Star Schema. That means:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;p&gt;One central fact table&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;Several surrounding dimension tables&lt;/p&gt;&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;All relationships are one-to-many, going from dimension to fact&lt;/p&gt;

&lt;p&gt;Why do we like this structure?&lt;/p&gt;

&lt;p&gt;✅ Better performance&lt;/p&gt;

&lt;p&gt;✅ Easier to understand and visualize&lt;/p&gt;

&lt;p&gt;✅ Works well with how Power BI handles filter context and measures&lt;/p&gt;

&lt;p&gt;Think of it like this: your fact table holds the numbers (sales, events, transactions), while your dimension tables give those numbers meaning (products, dates, customers).&lt;/p&gt;

&lt;p&gt;Avoid the “Snowflake Schema” unless you really need it. It might look neat with all those normalized layers, but it adds complexity, slows things down, and makes debugging harder.&lt;/p&gt;

&lt;p&gt;When in doubt, keep it simple. A star is all you need.&lt;/p&gt;

</description>
      <category>productivity</category>
      <category>tutorial</category>
      <category>beginners</category>
      <category>database</category>
    </item>
    <item>
      <title>Getting started with Excel for Data Analytics: From Basics to Data Cleaning</title>
      <dc:creator>Adrian Mageto</dc:creator>
      <pubDate>Sat, 29 Aug 2026 23:57:19 +0000</pubDate>
      <link>https://dev.to/adrian_mageto/getting-started-with-excel-for-data-analytics-from-basics-to-data-cleaning-3ecg</link>
      <guid>https://dev.to/adrian_mageto/getting-started-with-excel-for-data-analytics-from-basics-to-data-cleaning-3ecg</guid>
      <description>&lt;h1&gt;
  
  
  Introduction
&lt;/h1&gt;

&lt;p&gt;Excel is a program developed by Microsoft. Excel is used to organize data in column and rows and allows you to do mathematical functions.&lt;/p&gt;

&lt;p&gt;Excel is typically used for :&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Cleaning data&lt;/li&gt;
&lt;li&gt;Data entry &lt;/li&gt;
&lt;li&gt;Visuals and graphs&lt;/li&gt;
&lt;li&gt;Financial modeling&lt;/li&gt;
&lt;li&gt;Data analysis&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Overview
&lt;/h2&gt;

&lt;p&gt;This chapter is to give you an overview of Excel. Excel is made up of the Ribbon and the Sheet.&lt;/p&gt;

&lt;p&gt;At the picture below, the ribbon is marked with a red rectangle and the sheet is marked with a green rectangle:&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F0fjilfi55jp7ubcnbko9.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F0fjilfi55jp7ubcnbko9.png" alt="Excel spreadsheet showing the ribbon highlighted in a red rectangle" width="799" height="417"&gt;&lt;/a&gt; &lt;/p&gt;

&lt;h2&gt;
  
  
  Ribbon
&lt;/h2&gt;

&lt;p&gt;The ribbon contains App launcher, Tabs ,Commands as shown in the picture below.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F2d07oaw4es9zckjqeubm.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F2d07oaw4es9zckjqeubm.png" alt=" " width="800" height="161"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  The Sheet Explained
&lt;/h2&gt;

&lt;p&gt;The &lt;strong&gt;Sheet&lt;/strong&gt; is a set of row and columns. It forms the same pattern as we have in math exercise books, the rectangle boxes formed by the pattern are called cells.&lt;br&gt;
Have a look at the picture below. Hello World was typed in cell &lt;em&gt;C4&lt;/em&gt;. The reference can be found by clicking on the relevant cell and seeing the reference in the &lt;strong&gt;Name Box&lt;/strong&gt; to the left, which tells you the cell address is &lt;em&gt;C4&lt;/em&gt;.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Feuv9hlc8kem9yrcc103n.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Feuv9hlc8kem9yrcc103n.png" alt=" " width="800" height="441"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Data Types
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Text: Aligned to the left by default.&lt;/li&gt;
&lt;li&gt;Numbers: Aligned to the right by default used in numeric calculations.&lt;/li&gt;
&lt;li&gt;Currency: Formatted numbers displaying currency symbols or standard calender dates.&lt;/li&gt;
&lt;li&gt;Percentanges: Decimal values  displayed as parts of 100.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Data Organization :Sorting, Filtering and Tables
&lt;/h2&gt;

&lt;p&gt;This allows for quick exploration without changing the original values. &lt;/p&gt;

&lt;h2&gt;
  
  
  Sorting and Filtering
&lt;/h2&gt;

&lt;p&gt;Ranges can be sorted using the*&lt;em&gt;Sort Ascending&lt;/em&gt;* and &lt;strong&gt;Sort Descending&lt;/strong&gt; commands.&lt;br&gt;
&lt;strong&gt;Sort Ascending&lt;/strong&gt;: from smallest to largest.&lt;br&gt;
&lt;strong&gt;Sort Descending&lt;/strong&gt;: from largest to smallest.&lt;br&gt;
The sort command work for text too, using A-Z order.&lt;br&gt;
The commands are found in the Ribbon under the &lt;strong&gt;Sort &amp;amp; Filter&lt;/strong&gt; menu.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fwdhdj14qkkxnx3p9pyca.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fwdhdj14qkkxnx3p9pyca.png" alt="Excel spreadsheet showing how data is sorted" width="800" height="240"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;This also means that numbers can also be sorted according to the formatted data type whether: dates, currency or age.&lt;/p&gt;

&lt;h3&gt;
  
  
  Filtering
&lt;/h3&gt;

&lt;p&gt;Filter is similar to formatting a table, but it can be applied and deactivated.&lt;br&gt;
The menu is accessed in the default Ribbon view or in the data section in the navigation bar. &lt;br&gt;
Filters are applied by selecting a range and clicking the filter command.&lt;br&gt;
It is important to have a row of headers when applying filters. Having headers is useful to make the data understandable.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fxkuv7f5mut2srr1lxfkl.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fxkuv7f5mut2srr1lxfkl.png" alt="An excel mock dataset used to show how to filter the data" width="800" height="319"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Data Cleaning Tools
&lt;/h2&gt;

&lt;p&gt;Important Data Cleaning techniques:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Handle missing values: Filter to isolate blank cells, or highlight them.&lt;/li&gt;
&lt;li&gt;Removing Duplicates: Select your table go to Data&amp;gt; Remove Duplicates, and choose key columns.&lt;/li&gt;
&lt;li&gt;Trimming Extra Spaces: Using the function &lt;code&gt;=TRIM(cell address)&lt;/code&gt;
&lt;/li&gt;
&lt;li&gt;Using the Find &amp;amp; Replace command&lt;code&gt;(CTRL+H)&lt;/code&gt;to swap out typos or unnecessary additional texts.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;###Excel Conditional Formatting&lt;br&gt;
Conditional formatting is used to change the appearance of cells in range based on your specified &lt;strong&gt;conditions&lt;/strong&gt;.&lt;br&gt;
The conditions are rules based on specified numerical values or matching text.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F2ojjrk47cs6n8o65x40j.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F2ojjrk47cs6n8o65x40j.png" alt="Built-in conditions and appearances " width="439" height="600"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Conditional formatting is used to :&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Highlight duplicate cells.&lt;/li&gt;
&lt;li&gt;Highlight blank cells.&lt;/li&gt;
&lt;li&gt;Highlight values above or below a certain number.&lt;/li&gt;
&lt;li&gt;Can be used to create data bars and set icons.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Data Validation
&lt;/h2&gt;

&lt;p&gt;Feature used to control and restrict the type of data entered into a cell.&lt;br&gt;
Data Validation can be used to:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Create drop-down lists&lt;/li&gt;
&lt;li&gt;Allow only whole numbers within a specific range.&lt;/li&gt;
&lt;li&gt;Restrict entries  to dates or specific texts lengths.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  PivotTable
&lt;/h2&gt;

&lt;p&gt;Is a functionality in Excel which helps you organize and analyze data.&lt;br&gt;
It lets you add and remove values, perform calculations, and filter and sort data sets.&lt;br&gt;
To create one ;&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Click on a data set, and select &lt;code&gt;Insert &amp;gt; PivotTable&lt;/code&gt;.
PivotTables have components:&lt;/li&gt;
&lt;/ol&gt;

&lt;h3&gt;
  
  
  Columns
&lt;/h3&gt;

&lt;p&gt;Columns are vertical tabular data.&lt;br&gt;
The column includes the unique header, which is on the top.&lt;br&gt;
The header defines which data you are seeing listed downwards. &lt;/p&gt;

&lt;h3&gt;
  
  
  Rows
&lt;/h3&gt;

&lt;p&gt;Rows are horizontal tabular data.&lt;br&gt;
Data in the same row are related.&lt;/p&gt;

&lt;h3&gt;
  
  
  Filters
&lt;/h3&gt;

&lt;p&gt;Filters are used to select what data you see.&lt;/p&gt;

&lt;h3&gt;
  
  
  Values
&lt;/h3&gt;

&lt;p&gt;Values define how you present the data.&lt;br&gt;
You can define how you Summarize and Show values.&lt;/p&gt;

&lt;h2&gt;
  
  
  Fields and layout
&lt;/h2&gt;

&lt;p&gt;The TablePivot is displayed how by your settings.&lt;br&gt;
The PivotTable Fields panel is used to change how you see the data.&lt;br&gt;
The settings can be separated in two: Fields and Layout.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fpx7twkejyrafcrkuambz.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fpx7twkejyrafcrkuambz.png" alt="Data set showing the pivottable field layout" width="800" height="312"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h3&gt;
  
  
  Fields
&lt;/h3&gt;

&lt;p&gt;The checkboxes can be selected or unselected to display or change the property of the data.&lt;/p&gt;

&lt;p&gt;In this example, the checkbox for Speed is selected.&lt;/p&gt;

&lt;p&gt;Speed is now displayed in the table.&lt;/p&gt;

&lt;p&gt;![ ](&lt;a href="https://dev-to-uploads.s3.us-east-2.amazonaws.com" rel="noopener noreferrer"&gt;https://dev-to-uploads.s3.us-east-2.amazonaws.com&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;You can click the downwards arrow to change how the data is presented.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F69gedk5opneqn3yl6qn4.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F69gedk5opneqn3yl6qn4.png" alt=" " width="463" height="486"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h3&gt;
  
  
  Layout
&lt;/h3&gt;

&lt;p&gt;Drag and drop fields to the boxes to the right to display data in the table.&lt;/p&gt;

&lt;p&gt;You can drag them to the four different boxes that we mentioned earlier (four main components):&lt;/p&gt;

&lt;p&gt;Filters&lt;br&gt;
Rows&lt;br&gt;
Columns&lt;br&gt;
Values&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fuykclb145msyj4dmi1il.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fuykclb145msyj4dmi1il.png" alt=" " width="444" height="466"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Excel Functions
&lt;/h2&gt;

&lt;h3&gt;
  
  
  AVERAGE Function
&lt;/h3&gt;

&lt;p&gt;The AVERAGE function is a premade function in Excel, which calculates the average (arithmetic mean).&lt;/p&gt;

&lt;p&gt;It is typed &lt;code&gt;=AVERAGE()&lt;/code&gt;&lt;br&gt;
`&lt;br&gt;
It adds the range and divides it by the number of observations.&lt;/p&gt;

&lt;p&gt;Example:&lt;/p&gt;

&lt;p&gt;The average of (2, 3, 4) is 3.&lt;br&gt;
3 observations (2, 3 and 4)&lt;br&gt;
The sum of the observations (2 + 3 + 4 = 9)&lt;br&gt;
(9 / 3 = 3)&lt;br&gt;
The average is 3&lt;/p&gt;

&lt;h3&gt;
  
  
  CONCAT function
&lt;/h3&gt;

&lt;p&gt;CONCAT is a function in Excel and is short for concatenate.&lt;/p&gt;

&lt;p&gt;The CONCAT function is used to link multiple cells without adding any delimiters between the combined cell values.&lt;/p&gt;

&lt;p&gt;It is typed &lt;code&gt;=CONCAT()&lt;/code&gt;&lt;br&gt;
`&lt;/p&gt;

&lt;h3&gt;
  
  
  COUNT Function
&lt;/h3&gt;

&lt;p&gt;The COUNT function is a premade function in Excel, which counts cells with numbers in a range.&lt;/p&gt;

&lt;p&gt;It is typed &lt;code&gt;=COUNT()&lt;/code&gt;&lt;/p&gt;

&lt;p&gt;Note: The COUNT function only counts cells with numbers, not cells with letters. &lt;/p&gt;

&lt;h3&gt;
  
  
  COUNTA Function
&lt;/h3&gt;

&lt;p&gt;The COUNTA function is a premade function in Excel, which counts all cells in a range that has values, both numbers and letters.&lt;/p&gt;

&lt;p&gt;It is typed &lt;code&gt;=COUNTA()&lt;/code&gt;&lt;/p&gt;

&lt;h3&gt;
  
  
  COUNTBLANK Function
&lt;/h3&gt;

&lt;p&gt;The &lt;strong&gt;COUNTBLANK&lt;/strong&gt; function is a premade function in Excel, which counts blank cells in a range.&lt;/p&gt;

&lt;p&gt;It is typed &lt;code&gt;=COUNTBLANK()&lt;/code&gt;&lt;br&gt;
`&lt;br&gt;
&lt;strong&gt;Note&lt;/strong&gt;: The &lt;strong&gt;COUNTBLANK&lt;/strong&gt; function is helpful to find empty cells in a range.&lt;/p&gt;

&lt;h3&gt;
  
  
  COUNTIF Function
&lt;/h3&gt;

&lt;p&gt;The COUNTIF function is a premade function in Excel, which counts cells as specified.&lt;/p&gt;

&lt;p&gt;It is typed &lt;code&gt;=COUNTIF()&lt;/code&gt;&lt;/p&gt;

&lt;h3&gt;
  
  
  COUNTIFS
&lt;/h3&gt;

&lt;p&gt;The COUNTIFS function is a premade function in Excel, which counts cells in a range based on one or more true or false condition.&lt;/p&gt;

&lt;p&gt;It is typed &lt;code&gt;=COUNTIFS&lt;/code&gt;:&lt;/p&gt;

&lt;p&gt;&lt;code&gt;=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)&lt;/code&gt;&lt;br&gt;
The conditions are referred to as critera1, criteria2, .. and so on, which can check things like:&lt;/p&gt;

&lt;p&gt;If a number is greater than another number &amp;gt;&lt;br&gt;
If a number is smaller than another number &amp;lt;&lt;br&gt;
If a number or text is equal to something =&lt;br&gt;
The criteria_range1, criteria_range2, and so on, are the ranges where the function check for the conditions.&lt;/p&gt;

&lt;h3&gt;
  
  
  SUMIF Function
&lt;/h3&gt;

&lt;p&gt;The SUMIF function is a premade function in Excel, which calculates the sum of values in a range based on a true or false condition.&lt;/p&gt;

&lt;p&gt;It is typed &lt;code&gt;=SUMIF&lt;/code&gt;:&lt;/p&gt;

&lt;p&gt;&lt;code&gt;=SUMIF(range, criteria, [sum_range])&lt;br&gt;
&lt;/code&gt;The condition is referred to as criteria, which can check things like:&lt;/p&gt;

&lt;p&gt;If a number is greater than another number &amp;gt;&lt;br&gt;
If a number is smaller than another number &amp;lt;&lt;br&gt;
If a number or text is equal to something =&lt;br&gt;
The [sum_range] is the range where the function calculates the sum.      &lt;/p&gt;

&lt;h3&gt;
  
  
  SUMIFS Function
&lt;/h3&gt;

&lt;p&gt;The SUMIFS function is a premade function in Excel, which calculates the sum of a range based on one or more true or false condition.&lt;/p&gt;

&lt;p&gt;It is typed &lt;code&gt;=SUMIFS&lt;/code&gt;:&lt;/p&gt;

&lt;p&gt;&lt;code&gt;=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2] ...)&lt;/code&gt;&lt;br&gt;
The conditions are referred to as criteria1, criteria2, and so on, which can check things like:&lt;/p&gt;

&lt;p&gt;If a number is greater than another number &amp;gt;&lt;br&gt;
If a number is smaller than another number &amp;lt;&lt;br&gt;
If a number or text is equal to something =&lt;br&gt;
The criteria_range1, criteria_range2, and so on, are the ranges where the function check for the conditions.&lt;/p&gt;

&lt;p&gt;The [sum_range] is the range where the function calculates the sum.&lt;/p&gt;

&lt;h3&gt;
  
  
  SUM Function
&lt;/h3&gt;

&lt;p&gt;The SUM function is a premade function in Excel, which adds numbers in a range.&lt;/p&gt;

&lt;p&gt;It is typed &lt;code&gt;=SUM&lt;/code&gt;&lt;/p&gt;

&lt;p&gt;Note: The &lt;code&gt;=SUM&lt;/code&gt; function adds cells in a range, both negative and positive.&lt;/p&gt;

&lt;p&gt;How to use the &lt;code&gt;=SUM&lt;/code&gt; function:&lt;/p&gt;

&lt;p&gt;Select a cell&lt;br&gt;
Type &lt;code&gt;=SUM&lt;/code&gt;&lt;br&gt;
Double click the SUM command&lt;br&gt;
Select a range&lt;br&gt;
Hit enter&lt;/p&gt;

&lt;h3&gt;
  
  
  MAX Function
&lt;/h3&gt;

&lt;p&gt;The MAX function is a premade function in Excel, which finds the highest number in a range.&lt;/p&gt;

&lt;p&gt;It is typed &lt;code&gt;=MAX&lt;/code&gt;&lt;/p&gt;

&lt;h3&gt;
  
  
  MIN Function
&lt;/h3&gt;

&lt;p&gt;The MIN function is a premade function in Excel, which finds the lowest number in a range.&lt;/p&gt;

&lt;p&gt;It is typed &lt;code&gt;=MIN&lt;/code&gt;&lt;/p&gt;

&lt;h3&gt;
  
  
  MODE Function
&lt;/h3&gt;

&lt;p&gt;The MODE function is a premade function in Excel, which is used to find the number seen most times.&lt;/p&gt;

&lt;p&gt;This function always returns a single number.&lt;/p&gt;

&lt;p&gt;It is typed &lt;code&gt;=MODE.SNGL&lt;/code&gt;&lt;br&gt;
It returns the most occurring number in a range or array.&lt;/p&gt;

&lt;h3&gt;
  
  
  MEDIAN Function
&lt;/h3&gt;

&lt;p&gt;The MEDIAN function is a premade function in Excel, which returns the middle value in the data.&lt;/p&gt;

&lt;p&gt;It is typed &lt;code&gt;=MEDIAN&lt;/code&gt;&lt;/p&gt;

&lt;h3&gt;
  
  
  LOWER Function
&lt;/h3&gt;

&lt;p&gt;The LOWER function is used to lowercase text in a cell.&lt;/p&gt;

&lt;p&gt;Changing the letter case of your cell values can be great when there is a lot of case inconsistency among the cell inputs or when preparing your dataset for case-sensitive usage.&lt;/p&gt;

&lt;p&gt;It is typed &lt;code&gt;=LOWER&lt;/code&gt;&lt;br&gt;
If you want to use the function on a single cell, write:&lt;/p&gt;

&lt;p&gt;&lt;code&gt;=LOWER(cell)&lt;/code&gt;&lt;/p&gt;

&lt;h3&gt;
  
  
  RIGHT Function
&lt;/h3&gt;

&lt;p&gt;The RIGHT function is used to retrieve a chosen amount of characters, counting from the right side of an Excel cell. The chosen number has to be greater than 0 and is set to 1 by default.&lt;/p&gt;

&lt;p&gt;It is typed &lt;code&gt;=RIGHT&lt;/code&gt;&lt;/p&gt;

&lt;h3&gt;
  
  
  LEFT Function
&lt;/h3&gt;

&lt;p&gt;The LEFT function is used to retrieve a chosen amount of characters, counting from the left side of an Excel cell. The chosen number has to be greater than 0 and is set to 1 by default.&lt;/p&gt;

&lt;p&gt;It is typed &lt;code&gt;=LEFT&lt;/code&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  CONCLUSION
&lt;/h2&gt;

&lt;p&gt;Excel is a useful tool for data analytics, especially for beginners. It helps users clean, organize, analyze, and visualize data efficiently. By mastering functions and features such as data validation, conditional formatting, and data cleaning, users can turn raw data into meaningful insights.&lt;/p&gt;

</description>
    </item>
    <item>
      <title>MY FIRST GITHUB PROJECT</title>
      <dc:creator>Adrian Mageto</dc:creator>
      <pubDate>Sat, 22 Aug 2026 15:16:04 +0000</pubDate>
      <link>https://dev.to/adrian_mageto/my-first-github-project-268j</link>
      <guid>https://dev.to/adrian_mageto/my-first-github-project-268j</guid>
      <description>&lt;h2&gt;
  
  
  Introduction
&lt;/h2&gt;

&lt;p&gt;In this article I will explain my practical experience of creating Git repository,connecting it to GitHub using SSH,commiting my files and pushing the project to a remote repository.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 1 :Creating My Local Project
&lt;/h2&gt;

&lt;p&gt;The first step is to create a folder through file explorer which as the best option for me and name it (my-project)&lt;br&gt;
Next step was to open GitBash and run the command &lt;/p&gt;

&lt;p&gt;&lt;code&gt;cd my- project&lt;/code&gt;&lt;br&gt;
To open the file directory&lt;/p&gt;
&lt;h2&gt;
  
  
  Step 2 : Initializing Git
&lt;/h2&gt;

&lt;p&gt;This is to tell Git that my my-project folder should  become a Git repository.&lt;br&gt;
To initialize Git I ran the command:&lt;/p&gt;

&lt;p&gt;&lt;code&gt;git init&lt;/code&gt; &lt;/p&gt;
&lt;h2&gt;
  
  
  Step 3:Repository Status Check
&lt;/h2&gt;

&lt;p&gt;After initializing Git, I used :&lt;/p&gt;

&lt;p&gt;&lt;code&gt;git status&lt;/code&gt;&lt;/p&gt;

&lt;p&gt;This command is very useful as it can be used throughout the process  it tells what's happening inside my repository.&lt;br&gt;
At some point people may encounter:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;On branch main
Nothing to commit, working on a tree clean
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;It could seem like an error,however I learned that this means Git has checked my project and found no changes that need to be committed.&lt;/p&gt;

&lt;p&gt;Another situation that I encountered is where Git told me that the older my project was already initialized .This taught me to use the command:&lt;/p&gt;

&lt;p&gt;&lt;code&gt;git status&lt;/code&gt;&lt;br&gt;
To understand the current status of my project.&lt;/p&gt;
&lt;h2&gt;
  
  
  Step 4:Connecting GitHub Using SSH
&lt;/h2&gt;

&lt;p&gt;The next thing I learnt was on how to generate SSH key which will be used to connect my computer securely to GitHub.&lt;br&gt;
To generate an SSH key I ran the command:&lt;/p&gt;

&lt;p&gt;&lt;code&gt;ssh-keygen -t ed25519 -C "your_email@example.com"&lt;/code&gt;&lt;br&gt;
Under the double quotes use the email used for your GitHub account.&lt;/p&gt;

&lt;p&gt;Then press entre to save it on the default location.&lt;br&gt;
You'll then be told to enter passphrase which is simply a password,create a simple one which you can memorize easily like 1234.Then entre it again when asked.&lt;/p&gt;

&lt;p&gt;You then start the SSH agent &lt;br&gt;
Run;&lt;br&gt;
&lt;code&gt;eval "$(ssh-agent -s)"&lt;/code&gt;&lt;/p&gt;

&lt;p&gt;Then add your SSH keyy&lt;br&gt;
Run:&lt;br&gt;
&lt;code&gt;ssh-add ~/ .ssh/id_ed2559&lt;/code&gt;&lt;br&gt;
It will ask you to entre the passphrase you created.&lt;br&gt;
Copy your public key y running the command:&lt;/p&gt;

&lt;p&gt;&lt;code&gt;cat ~ .ssh/id_ed25519 .pub&lt;/code&gt;&lt;/p&gt;

&lt;p&gt;Add the entire line generated by coping it then go on GitHub-Settings-SSH and GPG keys-New SSH key,give it a title and paste the public key and save it.&lt;/p&gt;

&lt;p&gt;Then test the connection ack in Git Bash through the command:&lt;br&gt;
&lt;code&gt;ssh -T git@ithub.com&lt;/code&gt;&lt;/p&gt;

&lt;p&gt;Step 5:Creating a Repository on GitHub&lt;/p&gt;

&lt;p&gt;Log in to your GitHub account on the far right click on your profile scroll down to repositories press the option.&lt;/p&gt;

&lt;p&gt;After that create your repository,give it a title, add a small description about it ,choose its visibility to public finally press create repository.&lt;/p&gt;
&lt;h2&gt;
  
  
  Step 5:Adding Files
&lt;/h2&gt;

&lt;p&gt;This means to prepare the files and add changes in the current directory.&lt;/p&gt;

&lt;p&gt;To do this one runs the command:&lt;/p&gt;

&lt;p&gt;&lt;code&gt;git add .&lt;/code&gt;&lt;br&gt;
I could then check the status again:&lt;/p&gt;

&lt;p&gt;&lt;code&gt;git status&lt;/code&gt;&lt;br&gt;
The files would appear as changes ready to be committed.&lt;/p&gt;
&lt;h2&gt;
  
  
  Step 6:Create a Commit
&lt;/h2&gt;

&lt;p&gt;This stage is like a saved checkpoint in my project.&lt;br&gt;
To create a Commit run the command:&lt;/p&gt;

&lt;p&gt;&lt;code&gt;git commit -m "Initial commit"&lt;/code&gt;&lt;/p&gt;

&lt;p&gt;The message "Initial commit" is just a commit statement which can be different to your preference.&lt;/p&gt;
&lt;h2&gt;
  
  
  Step 7:Connect the Local Repository to GitHub
&lt;/h2&gt;

&lt;p&gt;I connected my local repository to GitHub &lt;br&gt;
Firstly one should open GitHub go to where he repository you created is click on it then copy the SSH which is used as a location key.&lt;br&gt;
Then using the command :&lt;/p&gt;

&lt;p&gt;&lt;code&gt;git remote add origin (paste the SSH key copied from GitHub)&lt;/code&gt;&lt;/p&gt;

&lt;p&gt;Then to check whether the connection was established we use the command :&lt;/p&gt;

&lt;p&gt;&lt;code&gt;git remote -v&lt;/code&gt;&lt;br&gt;
Then to ensure your current branch is called main use the command:&lt;/p&gt;

&lt;p&gt;&lt;code&gt;git branch -M main&lt;/code&gt;&lt;/p&gt;
&lt;h2&gt;
  
  
  Step 8:Push the Project to GitHub
&lt;/h2&gt;

&lt;p&gt;To push the folder&lt;/p&gt;

&lt;p&gt;I used:&lt;br&gt;
&lt;code&gt;git push -u origin main&lt;/code&gt;&lt;/p&gt;

&lt;p&gt;The -u option sets the remote ranch as the upstream branch .This means that later I can usually use:&lt;/p&gt;

&lt;p&gt;&lt;code&gt;git push&lt;/code&gt;&lt;/p&gt;

&lt;p&gt;Then go to your GitHub and click on your repository, you will find the folder my-project inside your online GitHub repository.&lt;/p&gt;
&lt;h1&gt;
  
  
  Conclusion
&lt;/h1&gt;

&lt;p&gt;Creating my first GitHub project helped me move from simply  having  files on my computer to managing a project using a professional development workflow.&lt;/p&gt;

&lt;p&gt;To understand the Git command projects I came up with my own mnemonic "I See All Commits Reach Big Places"&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;git init
git status 
git add &lt;span class="nb"&gt;.&lt;/span&gt;
git commit&lt;span class="s2"&gt;"commit statement"&lt;/span&gt;
git remote add origin &amp;lt;repository-url&amp;gt;
git branch &lt;span class="nt"&gt;-M&lt;/span&gt; main
git push &lt;span class="nt"&gt;-u&lt;/span&gt; origin main
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



</description>
      <category>beginners</category>
      <category>git</category>
      <category>github</category>
      <category>tutorial</category>
    </item>
  </channel>
</rss>
