<?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: jmuthama12</title>
    <description>The latest articles on DEV Community by jmuthama12 (@jmuthama12).</description>
    <link>https://dev.to/jmuthama12</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%2F3838695%2F56b1e6af-87fb-4306-bebe-03373d5059d6.png</url>
      <title>DEV Community: jmuthama12</title>
      <link>https://dev.to/jmuthama12</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/jmuthama12"/>
    <language>en</language>
    <item>
      <title>JOINS ,CTES and Subqueries in SQL</title>
      <dc:creator>jmuthama12</dc:creator>
      <pubDate>Tue, 20 Oct 2026 18:45:55 +0000</pubDate>
      <link>https://dev.to/jmuthama12/joins-ctes-and-subqueries-in-sql-4jc6</link>
      <guid>https://dev.to/jmuthama12/joins-ctes-and-subqueries-in-sql-4jc6</guid>
      <description>&lt;p&gt;Functions, Joins and Common Table Expressions Through Hospital Data&lt;br&gt;
Introduction&lt;/p&gt;

&lt;p&gt;Working with a relational database involves more than retrieving records from individual tables. In practice, analysts need to combine information from different sources, compare records within groups, identify patterns over time and build queries that remain understandable as the analysis becomes more complex. SQL joins, window functions and Common Table Expressions (CTEs) provide different ways of solving these problems.&lt;/p&gt;

&lt;p&gt;The hospital database used in this article models patients, doctors, departments, diagnoses, procedures, wards and hospital visits. The visits table is particularly important because it connects patient activity to clinical and administrative information. Each visit records details such as the visit date, attending doctor, department, diagnosis, procedure, admission type, length of stay, insurance provider, total bill and patient satisfaction score.&lt;/p&gt;

&lt;p&gt;This structure makes it possible to move from simple questions, such as how much a patient was billed, to more meaningful analytical questions. Which doctor generates the highest billing? How does a patient's current bill compare with their previous visit? Which departments contribute the most revenue? How does a visit compare with other visits within the same department?&lt;/p&gt;

&lt;p&gt;Answering these questions requires an understanding of how SQL processes relationships, groups and analytical calculations.&lt;/p&gt;

&lt;p&gt;The examples below use PostgreSQL and the tables defined in the evenig schema.&lt;/p&gt;

&lt;p&gt;Understanding SQL joins through the relationships between hospital tables&lt;/p&gt;

&lt;p&gt;A relational database avoids storing all information in one enormous table. Instead, information is separated into related tables, with primary and foreign keys establishing the relationships between them. This reduces duplication and makes the data easier to maintain, but it also means that meaningful analysis often requires combining multiple tables.&lt;/p&gt;

&lt;p&gt;Consider the visits table. A visit contains a patient_id, but this identifier does not tell us the patient's name, county or age. Those attributes reside in the patients table. Similarly, the visit contains a doctor_id, while the doctor's name and specialty are stored in doctors.&lt;/p&gt;

&lt;p&gt;The join operation allows these records to be brought together through their relationships.&lt;/p&gt;

&lt;p&gt;SELECT&lt;br&gt;
    v.visit_id,&lt;br&gt;
    v.visit_date,&lt;br&gt;
    p.patient_name,&lt;br&gt;
    p.county,&lt;br&gt;
    d.doctor_name,&lt;br&gt;
    v.admission_type,&lt;br&gt;
    v.total_bill_kes&lt;br&gt;
FROM evenig.visits AS v&lt;br&gt;
INNER JOIN evenig.patients AS p&lt;br&gt;
    ON v.patient_id = p.patient_id&lt;br&gt;
INNER JOIN evenig.doctors AS d&lt;br&gt;
    ON v.doctor_id = d.doctor_id&lt;br&gt;
ORDER BY v.visit_date, v.visit_id;&lt;/p&gt;

&lt;p&gt;The query connects three tables to produce a single view of hospital activity. The visits table acts as the starting point, while patients and doctors provide descriptive information.&lt;/p&gt;

&lt;p&gt;The ON conditions are what make these relationships meaningful. Matching v.patient_id to p.patient_id ensures that a visit is associated with the correct patient. Matching v.doctor_id to d.doctor_id then identifies the doctor responsible for that visit.&lt;/p&gt;

&lt;p&gt;An important detail is that a patient can appear more than once in the output. Amina Otieno, for example, appears in visits 1001 and 1011. This is not a duplication error. It represents two separate visits by the same patient. The unit of analysis remains the visit, not the patient.&lt;/p&gt;

&lt;p&gt;This distinction becomes critical when calculating totals. Counting rows in the joined result measures visits, whereas counting distinct patient identifiers measures unique patients.&lt;/p&gt;

&lt;p&gt;How different join strategies change the result&lt;/p&gt;

&lt;p&gt;An INNER JOIN returns records for which the join condition matches on both sides. In this dataset, the foreign key constraints on visits.patient_id and visits.doctor_id ensure that each valid visit references an existing patient and doctor. Consequently, joining those tables through their keys should preserve the visit records.&lt;/p&gt;

&lt;p&gt;A LEFT JOIN, however, is useful when the analysis must retain all records from a chosen starting table, even if related information is missing.&lt;/p&gt;

&lt;p&gt;For example, a hospital administrator might want to identify doctors who have not attended any recorded visits.&lt;/p&gt;

&lt;p&gt;SELECT&lt;br&gt;
    d.doctor_id,&lt;br&gt;
    d.doctor_name,&lt;br&gt;
    COUNT(v.visit_id) AS total_visits&lt;br&gt;
FROM evenig.doctors AS d&lt;br&gt;
LEFT JOIN evenig.visits AS v&lt;br&gt;
    ON d.doctor_id = v.doctor_id&lt;br&gt;
GROUP BY&lt;br&gt;
    d.doctor_id,&lt;br&gt;
    d.doctor_name&lt;br&gt;
ORDER BY total_visits DESC;&lt;/p&gt;

&lt;p&gt;Starting from doctors ensures that every doctor is represented, including a doctor with no matching visit. For such a doctor, columns from visits would contain NULL.&lt;/p&gt;

&lt;p&gt;The choice of COUNT(v.visit_id) is deliberate. Since visit_id is non-null for actual visits, the expression counts matched visits but ignores the null placeholder produced by the left join. Using COUNT(*) instead would count the preserved doctor row even when no visit matched, incorrectly producing one visit for that doctor.&lt;/p&gt;

&lt;p&gt;The same principle applies to other hospital tables. A department can be joined to visits through department_id, a diagnosis through diagnosis_id, and a procedure through procedure_id.&lt;/p&gt;

&lt;p&gt;The difference between an inner join and a left join is therefore not merely syntactical. It determines which records remain visible in the result and, consequently, which entities are included in the analysis.&lt;/p&gt;

&lt;p&gt;Joining multiple tables for a hospital performance report&lt;/p&gt;

&lt;p&gt;A more realistic analytical query combines several relationships to create a report that clinicians and administrators can interpret.&lt;/p&gt;

&lt;p&gt;SELECT&lt;br&gt;
    v.visit_id,&lt;br&gt;
    v.visit_date,&lt;br&gt;
    p.patient_name,&lt;br&gt;
    d.doctor_name,&lt;br&gt;
    dep.department_name,&lt;br&gt;
    dg.diagnosis_name,&lt;br&gt;
    pr.procedure_name,&lt;br&gt;
    v.admission_type,&lt;br&gt;
    v.length_of_stay_days,&lt;br&gt;
    v.total_bill_kes,&lt;br&gt;
    v.patient_satisfaction_score&lt;br&gt;
FROM evenig.visits AS v&lt;br&gt;
JOIN evenig.patients AS p&lt;br&gt;
    ON v.patient_id = p.patient_id&lt;br&gt;
JOIN evenig.doctors AS d&lt;br&gt;
    ON v.doctor_id = d.doctor_id&lt;br&gt;
JOIN evenig.department AS dep&lt;br&gt;
    ON v.department_id = dep.department_id&lt;br&gt;
LEFT JOIN evenig.diagnosis AS dg&lt;br&gt;
    ON v.diagnosis_id = dg.diagnosis_id&lt;br&gt;
LEFT JOIN evenig.procedures AS pr&lt;br&gt;
    ON v.procedure_id = pr.procedure_id&lt;br&gt;
ORDER BY v.visit_date, v.visit_id;&lt;/p&gt;

&lt;p&gt;The result is a visit-level dataset that can support reporting, dashboard development and further analysis. The inner joins are appropriate for the required patient, doctor and department relationships. The left joins allow a visit to remain visible if diagnosis or procedure information is absent.&lt;/p&gt;

&lt;p&gt;The distinction matters because a missing diagnosis should not automatically cause a valid financial record to disappear from a hospital billing report.&lt;/p&gt;

&lt;p&gt;There is another subtle issue with joins: they can change the number of rows. Joining a visit to a table with several matching records can multiply that visit in the result. This commonly occurs when analysts join a transaction table to a one-to-many relationship without considering its cardinality.&lt;/p&gt;

&lt;p&gt;If a visit has two associated procedures in a separate procedure-details table, for example, the visit may appear twice. Summing the original visit bill after that join would overstate revenue. Analysts must therefore understand the relationship between tables and verify the grain of the result before aggregating financial measures.&lt;/p&gt;

&lt;p&gt;SQL window functions: analysing records without losing their detail&lt;/p&gt;

&lt;p&gt;Aggregation is useful when a report requires one result per group. A query using GROUP BY doctor_id, for instance, can calculate total revenue for each doctor. However, the individual visit records disappear from the final output because they have been reduced to grouped results.&lt;/p&gt;

&lt;p&gt;Window functions solve a different problem. They perform calculations across related rows while retaining the individual rows in the result.&lt;/p&gt;

&lt;p&gt;This makes them particularly useful for rankings, running totals, comparisons with previous records and identifying the relative position of each visit within a group.&lt;/p&gt;

&lt;p&gt;Ranking doctors' visits by billing value&lt;/p&gt;

&lt;p&gt;Suppose the hospital wants to identify the highest-billed visits handled by each doctor. A simple descending sort would show the largest bills across the entire hospital, but it would not establish each doctor's position relative to their own visits.&lt;/p&gt;

&lt;p&gt;SELECT&lt;br&gt;
    v.visit_id,&lt;br&gt;
    d.doctor_name,&lt;br&gt;
    v.total_bill_kes,&lt;br&gt;
    RANK() OVER (&lt;br&gt;
        PARTITION BY v.doctor_id&lt;br&gt;
        ORDER BY v.total_bill_kes DESC&lt;br&gt;
    ) AS billing_rank&lt;br&gt;
FROM evenig.visits AS v&lt;br&gt;
JOIN evenig.doctors AS d&lt;br&gt;
    ON v.doctor_id = d.doctor_id&lt;br&gt;
ORDER BY&lt;br&gt;
    d.doctor_name,&lt;br&gt;
    billing_rank;&lt;/p&gt;

&lt;p&gt;The window definition has two important parts. PARTITION BY v.doctor_id separates the visits into independent groups, one for each doctor. ORDER BY v.total_bill_kes DESC ranks the visits within each group from the largest bill to the smallest.&lt;/p&gt;

&lt;p&gt;The rank resets for every doctor. A bill of KSh 42,000 could rank highly among one doctor's visits without necessarily being one of the highest bills across the entire hospital.&lt;/p&gt;

&lt;p&gt;The choice of ranking function also matters. RANK() assigns equal ranks to tied values and leaves gaps in subsequent ranks. DENSE_RANK() also assigns equal ranks to ties but does not leave gaps. ROW_NUMBER() assigns a unique sequential number to every row, even when bills are equal. If a stable ordering is needed, a secondary column such as visit_id can be added to the window's ORDER BY.&lt;/p&gt;

&lt;p&gt;These functions answer different questions. Ranking identifies relative position, while row numbering identifies a particular sequence of records.&lt;/p&gt;

&lt;p&gt;Comparing each visit with the previous visit&lt;/p&gt;

&lt;p&gt;Window functions become even more valuable when the order of records matters. Suppose a hospital analyst wants to compare each patient's current bill with their previous recorded bill.&lt;/p&gt;

&lt;p&gt;SELECT&lt;br&gt;
    v.patient_id,&lt;br&gt;
    p.patient_name,&lt;br&gt;
    v.visit_id,&lt;br&gt;
    v.visit_date,&lt;br&gt;
    v.total_bill_kes,&lt;br&gt;
    LAG(v.total_bill_kes) OVER (&lt;br&gt;
        PARTITION BY v.patient_id&lt;br&gt;
        ORDER BY v.visit_date, v.visit_id&lt;br&gt;
    ) AS previous_bill,&lt;br&gt;
    v.total_bill_kes&lt;br&gt;
      - LAG(v.total_bill_kes) OVER (&lt;br&gt;
            PARTITION BY v.patient_id&lt;br&gt;
            ORDER BY v.visit_date, v.visit_id&lt;br&gt;
        ) AS bill_difference&lt;br&gt;
FROM evenig.visits AS v&lt;br&gt;
JOIN evenig.patients AS p&lt;br&gt;
    ON v.patient_id = p.patient_id&lt;br&gt;
ORDER BY&lt;br&gt;
    p.patient_name,&lt;br&gt;
    v.visit_date,&lt;br&gt;
    v.visit_id;&lt;/p&gt;

&lt;p&gt;LAG() accesses a value from a preceding row in the same window partition. Here, the partition is the patient, so a patient's bill is compared with that patient's previous visit rather than with another patient's bill.&lt;/p&gt;

&lt;p&gt;For Amina Otieno, visit 1001 has a bill of KSh 3,500 and visit 1011 has a bill of KSh 4,200. The second visit therefore has a bill difference of KSh 700.&lt;/p&gt;

&lt;p&gt;The first visit for each patient has no preceding visit within the available data, so previous_bill and bill_difference return NULL. This is a meaningful result, not an error.&lt;/p&gt;

&lt;p&gt;The ordering also needs to be deterministic. Ordering by date alone may be insufficient if several visits occur on the same date. Including visit_id provides a tie-breaker for this dataset. In a production system, the analyst should use the actual event timestamp or another reliable sequence field where necessary.&lt;/p&gt;

&lt;p&gt;The comparison reveals a change in the recorded bill, but it does not establish why the bill changed. A higher bill could result from a different diagnosis, procedure, admission type or treatment plan. SQL produces the evidence; further analysis is required to explain the cause.&lt;/p&gt;

&lt;p&gt;Calculating cumulative hospital revenue&lt;/p&gt;

&lt;p&gt;A running total provides another example of a window function preserving the detail of the underlying records.&lt;/p&gt;

&lt;p&gt;SELECT&lt;br&gt;
    visit_id,&lt;br&gt;
    visit_date,&lt;br&gt;
    total_bill_kes,&lt;br&gt;
    SUM(total_bill_kes) OVER (&lt;br&gt;
        ORDER BY visit_date, visit_id&lt;br&gt;
        ROWS BETWEEN UNBOUNDED PRECEDING&lt;br&gt;
                 AND CURRENT ROW&lt;br&gt;
    ) AS cumulative_billing&lt;br&gt;
FROM evenig.visits&lt;br&gt;
ORDER BY visit_date, visit_id;&lt;/p&gt;

&lt;p&gt;The cumulative total starts with the first visit and increases as each subsequent visit is processed. It allows an analyst to examine how recorded billing accumulates over the observation period.&lt;/p&gt;

&lt;p&gt;The explicit ROWS frame is significant. It specifies that the calculation should include every row from the beginning of the ordered result through the current row. Without an explicit frame, some window aggregates use a default frame that can treat rows with equal ordering values as peers. Stating the intended frame makes the calculation easier to understand and reduces ambiguity.&lt;/p&gt;

&lt;p&gt;The result is not a daily revenue summary. It is a visit-level sequence of cumulative billing. If the objective is to report one total per day, a grouped aggregation would be more appropriate.&lt;/p&gt;

&lt;p&gt;Window functions are most effective when the question requires context across multiple records but the output must retain the individual observations.&lt;/p&gt;

&lt;p&gt;Common Table Expressions: building complex queries in understandable stages&lt;/p&gt;

&lt;p&gt;As analytical questions become more demanding, a single SQL statement can become difficult to read. The problem is particularly noticeable when a calculation must first be performed at one level, such as per doctor, before another calculation is applied at a different level, such as ranking doctors.&lt;/p&gt;

&lt;p&gt;A Common Table Expression, or CTE, addresses this by allowing a query to define a named intermediate result using the WITH clause. The main query can then reference that result as though it were a temporary relation available within the statement.&lt;/p&gt;

&lt;p&gt;Consider the question of which doctors generate the highest total billing.&lt;/p&gt;

&lt;p&gt;WITH doctor_revenue AS (&lt;br&gt;
    SELECT&lt;br&gt;
        d.doctor_id,&lt;br&gt;
        d.doctor_name,&lt;br&gt;
        SUM(v.total_bill_kes) AS total_revenue,&lt;br&gt;
        COUNT(v.visit_id) AS total_visits&lt;br&gt;
    FROM evenig.doctors AS d&lt;br&gt;
    JOIN evenig.visits AS v&lt;br&gt;
        ON d.doctor_id = v.doctor_id&lt;br&gt;
    GROUP BY&lt;br&gt;
        d.doctor_id,&lt;br&gt;
        d.doctor_name&lt;br&gt;
)&lt;br&gt;
SELECT&lt;br&gt;
    doctor_name,&lt;br&gt;
    total_revenue,&lt;br&gt;
    total_visits,&lt;br&gt;
    RANK() OVER (&lt;br&gt;
        ORDER BY total_revenue DESC&lt;br&gt;
    ) AS revenue_rank&lt;br&gt;
FROM doctor_revenue&lt;br&gt;
ORDER BY revenue_rank, doctor_name;&lt;/p&gt;

&lt;p&gt;The CTE first establishes a doctor-level result. Each doctor has one row containing total billing and visit count. The outer query then ranks those doctor-level totals.&lt;/p&gt;

&lt;p&gt;The two stages operate at different grains. The inner query reduces visit-level data to one record per doctor, while the outer query compares those resulting doctor records. This separation is essential: ranking individual visits would answer a different question from ranking doctors by their total revenue.&lt;/p&gt;

&lt;p&gt;The CTE makes the analytical logic visible. A reader can inspect the revenue calculation independently from the ranking calculation, and changes to one stage are easier to reason about.&lt;/p&gt;

&lt;p&gt;A CTE is not, however, automatically a permanent table or a guaranteed performance optimisation. It exists for the duration of the SQL statement. PostgreSQL may inline a CTE into the surrounding query or materialise its result depending on the query, the PostgreSQL version and the planner's decisions. Performance should be assessed using the actual execution plan rather than assumed from the presence of WITH.&lt;/p&gt;

&lt;p&gt;Chaining CTEs for a more complete analysis&lt;/p&gt;

&lt;p&gt;CTEs can also be chained so that one stage builds on another. For example, an analyst may calculate department revenue first and then compare each department with the hospital-wide average.&lt;/p&gt;

&lt;p&gt;WITH department_revenue AS (&lt;br&gt;
    SELECT&lt;br&gt;
        dep.department_id,&lt;br&gt;
        dep.department_name,&lt;br&gt;
        SUM(v.total_bill_kes) AS total_revenue&lt;br&gt;
    FROM evenig.department AS dep&lt;br&gt;
    JOIN evenig.visits AS v&lt;br&gt;
        ON dep.department_id = v.department_id&lt;br&gt;
    GROUP BY&lt;br&gt;
        dep.department_id,&lt;br&gt;
        dep.department_name&lt;br&gt;
),&lt;br&gt;
hospital_average AS (&lt;br&gt;
    SELECT&lt;br&gt;
        AVG(total_revenue) AS average_department_revenue&lt;br&gt;
    FROM department_revenue&lt;br&gt;
)&lt;br&gt;
SELECT&lt;br&gt;
    dr.department_name,&lt;br&gt;
    dr.total_revenue,&lt;br&gt;
    ha.average_department_revenue,&lt;br&gt;
    dr.total_revenue&lt;br&gt;
        - ha.average_department_revenue AS difference_from_average&lt;br&gt;
FROM department_revenue AS dr&lt;br&gt;
CROSS JOIN hospital_average AS ha&lt;br&gt;
ORDER BY dr.total_revenue DESC;&lt;/p&gt;

&lt;p&gt;The first CTE calculates total revenue per department. The second derives the average of those department totals. The final query compares each department with that average.&lt;/p&gt;

&lt;p&gt;The CROSS JOIN is appropriate because hospital_average returns a single row, which is attached to each department record. The comparison is between department totals, not individual visit bills.&lt;/p&gt;

&lt;p&gt;That distinction illustrates why analytical stages should be designed deliberately. The average of department revenues is not the same as the average visit bill. One gives each department equal weight; the other gives each visit equal weight.&lt;/p&gt;

&lt;p&gt;CTEs versus subqueries: two approaches to structuring the same logic&lt;/p&gt;

&lt;p&gt;A subquery is a query nested inside another SQL statement. It can appear in a FROM clause, a WHERE condition, a SELECT expression or other permitted positions. CTEs and subqueries can often solve the same analytical problem, but their structure affects how easily a query can be understood and maintained.&lt;/p&gt;

&lt;p&gt;Take the task of identifying departments whose total billing exceeds the average department revenue.&lt;/p&gt;

&lt;p&gt;Using a CTE, the calculation can be separated into named stages:&lt;/p&gt;

&lt;p&gt;WITH department_revenue AS (&lt;br&gt;
    SELECT&lt;br&gt;
        department_id,&lt;br&gt;
        SUM(total_bill_kes) AS total_revenue&lt;br&gt;
    FROM evenig.visits&lt;br&gt;
    GROUP BY department_id&lt;br&gt;
)&lt;br&gt;
SELECT&lt;br&gt;
    dep.department_name,&lt;br&gt;
    dr.total_revenue&lt;br&gt;
FROM department_revenue AS dr&lt;br&gt;
JOIN evenig.department AS dep&lt;br&gt;
    ON dep.department_id = dr.department_id&lt;br&gt;
WHERE dr.total_revenue &amp;gt; (&lt;br&gt;
    SELECT AVG(total_revenue)&lt;br&gt;
    FROM department_revenue&lt;br&gt;
)&lt;br&gt;
ORDER BY dr.total_revenue DESC;&lt;/p&gt;

&lt;p&gt;The department_revenue CTE defines the department-level totals once. Both the outer query and the scalar subquery refer to those totals, making the intended level of comparison explicit.&lt;/p&gt;

&lt;p&gt;The same logic can be expressed with nested subqueries:&lt;/p&gt;

&lt;p&gt;SELECT&lt;br&gt;
    dep.department_name,&lt;br&gt;
    dr.total_revenue&lt;br&gt;
FROM evenig.department AS dep&lt;br&gt;
JOIN (&lt;br&gt;
    SELECT&lt;br&gt;
        department_id,&lt;br&gt;
        SUM(total_bill_kes) AS total_revenue&lt;br&gt;
    FROM evenig.visits&lt;br&gt;
    GROUP BY department_id&lt;br&gt;
) AS dr&lt;br&gt;
    ON dr.department_id = dep.department_id&lt;br&gt;
WHERE dr.total_revenue &amp;gt; (&lt;br&gt;
    SELECT AVG(x.total_revenue)&lt;br&gt;
    FROM (&lt;br&gt;
        SELECT&lt;br&gt;
            department_id,&lt;br&gt;
            SUM(total_bill_kes) AS total_revenue&lt;br&gt;
        FROM evenig.visits&lt;br&gt;
        GROUP BY department_id&lt;br&gt;
    ) AS x&lt;br&gt;
)&lt;br&gt;
ORDER BY dr.total_revenue DESC;&lt;/p&gt;

&lt;p&gt;Both statements express the same fundamental calculation: aggregate visits to the department level, calculate the average of those totals, and retain departments above that average. The subquery version repeats the aggregation logic, whereas the CTE version gives that logic a reusable name.&lt;/p&gt;

&lt;p&gt;This difference becomes more important as a query grows. Repeated subqueries can make it difficult to verify whether two calculations use identical filters and grouping rules. A CTE can make each analytical stage easier to inspect and modify.&lt;/p&gt;

&lt;p&gt;Nevertheless, it would be incorrect to conclude that CTEs are always faster or that subqueries are inherently inferior. PostgreSQL's query planner can optimise many equivalent expressions similarly. A CTE may be materialised in some circumstances, potentially affecting performance, while a subquery may be flattened into a larger query plan.&lt;/p&gt;

&lt;p&gt;The more useful distinction is one of query structure and semantics. CTEs are especially valuable when the query has several logical stages, an intermediate result needs to be referenced more than once, or the analysis benefits from clear named steps. Subqueries are often concise when the nested calculation is small and used only once.&lt;/p&gt;

&lt;p&gt;Neither technique should be selected purely for style without considering the required result, the complexity of the logic and, where performance matters, the execution plan.&lt;/p&gt;

&lt;p&gt;Bringing joins, window functions and CTEs together&lt;/p&gt;

&lt;p&gt;The real analytical value of these concepts emerges when they are used together. Consider a hospital report that ranks departments by total billing while also calculating each department's percentage contribution to all recorded billing.&lt;/p&gt;

&lt;p&gt;WITH department_summary AS (&lt;br&gt;
    SELECT&lt;br&gt;
        dep.department_id,&lt;br&gt;
        dep.department_name,&lt;br&gt;
        COUNT(v.visit_id) AS total_visits,&lt;br&gt;
        SUM(v.total_bill_kes) AS total_revenue,&lt;br&gt;
        AVG(v.patient_satisfaction_score)&lt;br&gt;
            AS average_satisfaction&lt;br&gt;
    FROM evenig.department AS dep&lt;br&gt;
    JOIN evenig.visits AS v&lt;br&gt;
        ON dep.department_id = v.department_id&lt;br&gt;
    GROUP BY&lt;br&gt;
        dep.department_id,&lt;br&gt;
        dep.department_name&lt;br&gt;
)&lt;br&gt;
SELECT&lt;br&gt;
    department_name,&lt;br&gt;
    total_visits,&lt;br&gt;
    total_revenue,&lt;br&gt;
    ROUND(average_satisfaction, 2) AS average_satisfaction,&lt;br&gt;
    RANK() OVER (&lt;br&gt;
        ORDER BY total_revenue DESC&lt;br&gt;
    ) AS revenue_rank,&lt;br&gt;
    ROUND(&lt;br&gt;
        100.0 * total_revenue&lt;br&gt;
        / NULLIF(SUM(total_revenue) OVER (), 0),&lt;br&gt;
        2&lt;br&gt;
    ) AS revenue_percentage&lt;br&gt;
FROM department_summary&lt;br&gt;
ORDER BY revenue_rank, department_name;&lt;/p&gt;

&lt;p&gt;The CTE combines the department and visit tables and aggregates their records into a department-level summary. The window functions then rank the departments and calculate each department's percentage of the combined revenue total.&lt;/p&gt;

&lt;p&gt;SUM(total_revenue) OVER () calculates the total of the department revenue values across the entire result without collapsing the department rows. NULLIF(..., 0) prevents division by zero if the total is zero, and multiplying by 100.0 ensures a decimal percentage calculation.&lt;/p&gt;

&lt;p&gt;The result supports two distinct interpretations. The ranking identifies the relative order of departments by billing, while the percentage indicates how much each department contributes to total recorded billing. A department can rank first without necessarily accounting for a majority of the hospital's billing.&lt;/p&gt;

&lt;p&gt;The average satisfaction score also requires careful interpretation. It is the average of the scores recorded for visits in each department, not necessarily an average of all patients' lifetime satisfaction. If the same patient has multiple visits, that patient can contribute multiple observations.&lt;/p&gt;

&lt;p&gt;This is why understanding the grain of the data is just as important as understanding SQL syntax. Every metric has a unit of analysis, and changing the joins, grouping level or filtering conditions can change what that metric represents.&lt;/p&gt;

</description>
      <category>data</category>
      <category>database</category>
      <category>sql</category>
      <category>tutorial</category>
    </item>
    <item>
      <title>Power Bi,Modelling,Relationships and Joins</title>
      <dc:creator>jmuthama12</dc:creator>
      <pubDate>Mon, 14 Sep 2026 11:11:20 +0000</pubDate>
      <link>https://dev.to/jmuthama12/power-bimodellingrelationships-and-joins-22h3</link>
      <guid>https://dev.to/jmuthama12/power-bimodellingrelationships-and-joins-22h3</guid>
      <description>&lt;p&gt;Power BI Data Modelling, Relationships and Joins: &lt;/p&gt;

&lt;p&gt;When I first started working with Power BI, I thought building a report was mainly about choosing charts. I quickly learned that the real work happens behind the visuals.&lt;/p&gt;

&lt;p&gt;A good Power BI report starts with a good data model.&lt;/p&gt;

&lt;p&gt;1.&lt;strong&gt;_ Data Modelling in Power BI_&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Data modelling is the process of organising tables and defining how they relate to each other. A good model improves DAX, performance, scalability, reporting and maintenance.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Flat Table&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Everything is stored in one large table.&lt;/p&gt;

&lt;p&gt;Sales → Customer + Product + Date + Location + Amount&lt;/p&gt;

&lt;p&gt;Pros: Simple and easy to understand.&lt;br&gt;
Cons: Repeated data, larger models and harder maintenance.&lt;/p&gt;

&lt;p&gt;It works for small datasets, but becomes inefficient as data grows.&lt;/p&gt;

&lt;p&gt;Star Schema&lt;/p&gt;

&lt;p&gt;A central fact table connects directly to several dimension tables.&lt;/p&gt;

&lt;p&gt;DimCustomer → FactSales ← DimProduct&lt;br&gt;
↑&lt;br&gt;
DimDate&lt;/p&gt;

&lt;p&gt;This is usually my preferred approach because it is simple, fast and DAX-friendly.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;&lt;em&gt;Snowflake Schema&lt;/em&gt;&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Dimensions are further split into related tables.&lt;/p&gt;

&lt;p&gt;DimCategory → DimProduct → FactSales&lt;br&gt;
DimCountry → DimCustomer → FactSales&lt;/p&gt;

&lt;p&gt;It reduces duplication but creates more relationships and complexity. It can be useful for large, highly normalised datasets, but I would normally prefer a star schema in Power BI.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Fact and Dimension Tables&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;A fact table records business events and numeric values.&lt;/p&gt;

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

&lt;p&gt;FactSales: SalesID, CustomerID, ProductID, DateID, Quantity, Revenue&lt;/p&gt;

&lt;p&gt;A dimension table describes those events.&lt;/p&gt;

&lt;p&gt;DimCustomer: CustomerID, Name, Region&lt;br&gt;
DimProduct: ProductID, ProductName, Category&lt;br&gt;
DimDate: DateID, Date, Month, Year&lt;/p&gt;

&lt;p&gt;The most important concept is grain—what one row represents. For example, one row in FactSales might represent one product sold in one transaction.&lt;/p&gt;

&lt;p&gt;3.&lt;strong&gt;_ Relationships_&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;A relationship tells Power BI how tables are connected.&lt;/p&gt;

&lt;p&gt;The common types are:&lt;/p&gt;

&lt;p&gt;1: One-to-Many:* one customer can have many sales. This is the normal star-schema relationship.&lt;br&gt;
1:1 One-to-One: each record matches one record. Useful only in specific cases.&lt;br&gt;
: Many-to-Many:* multiple records match multiple records. Use carefully because it can create ambiguous filtering.&lt;/p&gt;

&lt;p&gt;Usually, DimCustomer[CustomerID] is unique, while FactSales[CustomerID] can appear many times. The first is a primary key; the second is a foreign key.&lt;/p&gt;

&lt;p&gt;Active relationships are used by default. Inactive relationships can be activated in DAX when needed.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;&lt;strong&gt;&lt;em&gt;Filter Direction&lt;/em&gt;&lt;/strong&gt;&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;With single-direction filtering, a selection in DimProduct filters FactSales.&lt;/p&gt;

&lt;p&gt;DimProduct → FactSales&lt;/p&gt;

&lt;p&gt;Bidirectional filtering allows filters to travel both ways, but it should be used carefully. It can create ambiguous filter paths and unnecessary complexity.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;&lt;strong&gt;&lt;em&gt;Joins in Power Query&lt;/em&gt;&lt;/strong&gt;&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;A join combines tables using Merge Queries.&lt;/p&gt;

&lt;p&gt;Using Customers and Orders:&lt;/p&gt;

&lt;p&gt;Join    Records retained&lt;br&gt;
Left Outer  All Customers + matching Orders&lt;br&gt;
Right Outer All Orders + matching Customers&lt;br&gt;
Full Outer  All records from both&lt;br&gt;
Inner   Only matching records&lt;br&gt;
Left Anti   Customers without Orders&lt;br&gt;
Right Anti  Orders without Customers&lt;/p&gt;

&lt;p&gt;Joins are useful when you actually need to bring columns or rows together during data preparation.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;&lt;strong&gt;&lt;em&gt;Joins vs Relationships&lt;/em&gt;&lt;/strong&gt;&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;This distinction confused me at first.&lt;/p&gt;

&lt;p&gt;A Power Query merge physically combines data before it enters the model.&lt;/p&gt;

&lt;p&gt;A Power BI relationship does not combine tables. It simply tells Power BI how tables should filter each other.&lt;/p&gt;

&lt;p&gt;Therefore, I would avoid excessive merging. Keeping FactSales, DimCustomer, DimProduct and DimDate separate usually produces a cleaner model.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;My Recommended Model&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;For most business intelligence projects, I would choose a Star Schema:&lt;/p&gt;

&lt;p&gt;Dimensions → Fact Table&lt;/p&gt;

&lt;p&gt;I would normally use 1: relationships*, with dimensions on the “1” side and facts on the “*” side, using single-direction filtering.&lt;/p&gt;

&lt;p&gt;Why?&lt;/p&gt;

&lt;p&gt;It gives me better performance, simpler DAX, less redundancy, easier reporting, clearer relationships and easier maintenance.&lt;/p&gt;

&lt;p&gt;Power BI is not just about making beautiful dashboards. A beautiful dashboard built on a poor model will eventually become a painful dashboard to maintain.&lt;/p&gt;

&lt;p&gt;Build the model well, and the report becomes much easier.&lt;/p&gt;

</description>
      <category>analytics</category>
      <category>data</category>
      <category>database</category>
    </item>
    <item>
      <title>EXCEL AS A DATA ANALYSIS TOOL</title>
      <dc:creator>jmuthama12</dc:creator>
      <pubDate>Sun, 22 Mar 2026 18:11:32 +0000</pubDate>
      <link>https://dev.to/jmuthama12/excel-as-a-data-analysis-tool-437n</link>
      <guid>https://dev.to/jmuthama12/excel-as-a-data-analysis-tool-437n</guid>
      <description>&lt;p&gt;Microsoft Excel is one of the most important tools in data analysis. Whether you're cleaning data, building reports, or preparing datasets for pipelines, Excel skills are essential.&lt;br&gt;
&lt;strong&gt;BASIC EXCEL FUNCTIONS&lt;/strong&gt;&lt;br&gt;
➤ SUM&lt;br&gt;
Adds values in a range&lt;br&gt;
=SUM(A1:A10)&lt;/p&gt;

&lt;p&gt;➤ AVERAGE&lt;/p&gt;

&lt;p&gt;Finds the mean&lt;br&gt;
=AVERAGE(A1:A10)&lt;br&gt;
&lt;strong&gt;COUNT&lt;/strong&gt;&lt;br&gt;
Counts numeric values&lt;br&gt;
=COUNT(A1:A10)&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Logical Functions&lt;/strong&gt;&lt;br&gt;
➤ IF&lt;br&gt;
=IF(A1&amp;gt;50, "Pass", "Fail")&lt;/p&gt;

&lt;p&gt;Returns values based on a condition&lt;/p&gt;

&lt;p&gt;➤ AND&lt;br&gt;
=AND(A1&amp;gt;50, B1="Yes")&lt;/p&gt;

&lt;p&gt;Checks multiple conditions&lt;br&gt;
➤ OR&lt;br&gt;
=OR(A1&amp;gt;50, B1="Yes")&lt;/p&gt;

&lt;p&gt;Returns TRUE if one condition is met&lt;/p&gt;

&lt;p&gt;** Lookup &amp;amp; Reference Functions**&lt;br&gt;
➤ VLOOKUP&lt;br&gt;
=VLOOKUP(A2, A1:C10, 3, FALSE)&lt;/p&gt;

&lt;p&gt;Searches vertically&lt;br&gt;
➤ HLOOKUP&lt;br&gt;
=HLOOKUP(A2, A1:C3, 2, FALSE)&lt;/p&gt;

&lt;p&gt;Searches horizontally&lt;br&gt;
➤ INDEX + MATCH (Better alternative)&lt;br&gt;
=INDEX(B1:B10, MATCH(A2, A1:A10, 0))&lt;br&gt;
➤ &lt;strong&gt;LEN&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Counts characters&lt;br&gt;
=LEN(A1)&lt;br&gt;
&lt;strong&gt;CONCAT / CONCATENATE&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Join text&lt;br&gt;
=CONCAT(A1, " ", B1)&lt;/p&gt;

&lt;p&gt;These are some of the formulae's used daily in excel &lt;/p&gt;

</description>
    </item>
  </channel>
</rss>
