---
title: "Calculate Match Percentage in PySpark SQL"
description: "The provided PySpark SQL query calculates the match percentage of a specific column (s_code) between two tables, Table1 and Table2. It uses Common Table Expressions (CTEs) to extract unique values, count matches, and compute the percentage based on total unique entries. The resulting output reflects the matching statistics."
url: https://srinimf.com/2024/12/22/calculate-match-percentage-in-pyspark-sql/
date: 2024-12-22
modified: 2026-10-09
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2024/12/image-5.png?fit=1024%2C768&ssl=1
categories: ["PySpark"]
tags: ["SQL Match Percentage"]
type: post
lang: en
---

# Calculate Match Percentage in PySpark SQL

This is a PySpark SQL query. It provides a match percentage for a specific column when comparing two tables, Table1 and Table2.

## PySpark SQL for Match Percentage

```
WITH distinct_table1 AS (
    SELECT DISTINCT sku_code
    FROM table1
    WHERE s_code IS NOT NULL
),
distinct_table2 AS (
    SELECT DISTINCT sku_code
    FROM table2
    WHERE s_code IS NOT NULL
),
match_counts AS (
    SELECT 
        COUNT(*) AS matching_count
    FROM 
        distinct_table2 t2
    INNER JOIN 
        distinct_table1 t1
    ON 
        t2.s_code = t1.s_code
),
total_table1 AS (
    SELECT 
        COUNT(*) AS total_count
    FROM 
        distinct_table1
),
total_table2 AS (
    SELECT 
        COUNT(*) AS total_count
    FROM 
        distinct_table2
)
SELECT 
    mc.matching_count,
    tt1.total_count AS total_table1_count,
    tt2.total_count AS total_table2_count,
    CASE 
        WHEN tt2.total_count = 0 THEN 0  -- Handle division by zero
        ELSE ROUND((mc.matching_count * 100.0) / tt2.total_count, 2)
    END AS match_percentage
FROM 
    match_counts mc
CROSS JOIN 
    total_table1 tt1
CROSS JOIN 
    total_table2 tt2;
```

> Databricks Top Course

### Explanation

1. **`distinct_table1` CTE**:
   - Extracts unique `s_code` values from **Table 1**, removing duplicates and filtering out `NULL` values.
2. **`distinct_table2` CTE**:
   - Extracts unique `s_code` values from **Table 2**, removing duplicates and filtering out `NULL` values.
3. **`match_counts` CTE**:
   - Joins the `distinct_table1` and `distinct_table2` on `s_code` to count only unique matches.
4. **`total_table1` and `total_table2` CTEs**:
   - Calculate the total number of unique `s_code` values in **Table 1** and **Table 2**, respectively.
5. **Main Query**:
   - Combines the counts and calculates the match percentage based on unique `s_code` values in **Table 2**.

### Example Data

#### Table 1 (Master):

| s_code |
| --- |
| A123 |
| B456 |
| C789 |
| D012 |
| A123 |

#### Table 2:

| s_code |
| --- |
| A123 |
| C789 |
| X999 |
| A123 |

---

### Output:

| matching_count | total_table1_count | total_table2_count | match_percentage |
| --- | --- | --- | --- |
| 2 | 4 | 3 | 66.67 |

References

- [PySPARK SQL Presentation](https://courses.grainger.illinois.edu/cs398acc/sp2018/slides/Lecture%207.pdf)
- [Lambda Architecture](https://courses.cs.duke.edu/spring15/compsci516/Lectures/LanceIntroSpark.pdf)

Related[IT Project (Mainframe) – Risk](https://srinimf.com/2011/07/23/it-project-mainframe-risk/)

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2024%2F12%2F22%2Fcalculate-match-percentage-in-pyspark-sql%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2024%2F12%2Fimage-5.png%3Ffit%3D1024%252C768%26ssl%3D1&description=Calculate%20Match%20Percentage%20in%20PySpark%20SQL)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2024/12/22/calculate-match-percentage-in-pyspark-sql/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2024/12/22/calculate-match-percentage-in-pyspark-sql/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2024/12/22/calculate-match-percentage-in-pyspark-sql/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2024/12/22/calculate-match-percentage-in-pyspark-sql/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20Calculate%20Match%20Percentage%20in%20PySpark%20SQL&body=https%3A%2F%2Fsrinimf.com%2F2024%2F12%2F22%2Fcalculate-match-percentage-in-pyspark-sql%2F&share=email)
-

### Like this:

Like Loading…