Project typeTechnical notebook
Technical notebook, SQL

SQL Practice

I used T-SQL to show how often each car was test-driven and by how many people, alongside the number of buyers recorded for that car.

SkillsQuery writing, relational data analysis
TimelineApr 2022
Methods & toolsT-SQL, joins, aggregation, functions

How many people test-drove each vehicle?

A customer can drive the same car twice, and a sale can have multiple buyers. The report needs separate counts for visits and people, with each total tied to the individual car in inventory.

How I counted

I count test drives and buyers separately before combining the totals for each car. That way, a sale with two buyers cannot make the same test drive appear twice.

Vehicle activity by inventory item

T-SQL
;WITH TestDriveCounts AS (
    SELECT
        [Inventory code],
        COUNT(DISTINCT [Test drive code]) AS TestDrives,
        COUNT(DISTINCT [Customer number]) AS TestDriveCustomers
    FROM TESTDRIVE
    GROUP BY [Inventory code]
),
BuyerCounts AS (
    SELECT
        [Inventory code],
        COUNT(DISTINCT [Customer number]) AS Buyers
    FROM SALES
    GROUP BY [Inventory code]
)
SELECT
    i.[Inventory code],
    i.[vin number],
    v.[Year],
    v.Make,
    v.Model,
    COALESCE(td.TestDrives, 0) AS TestDrives,
    COALESCE(td.TestDriveCustomers, 0) AS TestDriveCustomers,
    COALESCE(b.Buyers, 0) AS Buyers
FROM INVENTORY AS i
LEFT JOIN VEHICLE AS v ON v.[vehicle code] = i.[vehicle code]
LEFT JOIN TestDriveCounts AS td ON td.[Inventory code] = i.[Inventory code]
LEFT JOIN BuyerCounts AS b ON b.[Inventory code] = i.[Inventory code]
ORDER BY i.[Inventory code];

What the counts tell us

Repeat drives

If one customer comes back for a second drive, that adds another test drive. The number of distinct test-drive customers stays at 1.

Cars with no activity

Starting from inventory keeps every car in the report. A car with no test-drive or sales records shows 0 in the corresponding counts.

Purchase conversion

The buyer count includes everyone recorded on the sale. To measure test-drive conversion, I would match customers across the two tables for the same car and check that the drive came before the sale.