Concepts /
HCPCS (Level I and Level II)
The code system for what was done or supplied: services, drugs, equipment.
HCPCS (Healthcare Common Procedure Coding System, said "hick-picks") has two levels.
Level I is CPT® (Current Procedural Terminology), a numeric code set maintained (and licensed) by the American Medical Association. This site doesn't show CPT codes or descriptions.
Level II is maintained by CMS. It covers products, supplies and services that Level I doesn't, such as ambulance services, durable medical equipment and many drugs (for example, J codes for drugs). Level II codes are a letter followed by four digits.
CMS publishes the Level II code file publicly and updates it every quarter.
In the tables
| Column | Tables | Meaning |
|---|---|---|
hcpcs_code | provider_services_hi, hcpcs_level2 | The billed HCPCS code; join key between the services table and the Level II code file. |
short_description | hcpcs_level2 | CMS's abbreviated code description (for drug codes it includes the billing unit, such as 1 mg). |
long_description | hcpcs_level2 | CMS's full code description. |
effective_date | hcpcs_level2 | Date the code's current entry took effect in the CMS file. |
services | provider_services_hi | Number of services billed for that NPI, code and place of service. |
Formats
- Level II: exactly 5 characters, one letter followed by four digits (for example J3301 or G0008). Store it as text (VARCHAR).
- Level I (CPT®): 5 numeric digits, maintained by the American Medical Association. This site names CPT but shows no CPT codes or descriptions.
- Codes that share a first letter tend to be related. Read the descriptions in hcpcs_level2 to see it: in this cut, the J codes billed are drugs (injections, implants, inhalation solutions and topicals), and the G codes include visits, screenings and vaccine administration.
Common pitfalls
- A code alone is not a count of people: provider_services_hi has one row per NPI, code and place of service, so use COUNT(DISTINCT npi) to count providers.
- For drug codes, services counts billing units defined by the description (such as per 1 mg), not doses or patients.
- CMS adds, revises and deletes Level II codes in quarterly updates. When you join billed codes to a code file, check for codes with no match (LEFT JOIN … WHERE h.hcpcs_code IS NULL).
- Comparing codes as numbers or with stray spaces or lowercase letters breaks joins. Compare exact uppercase text.
Beginner queries
DuckDB SQL against this site's Parquet tables. Illustrative — the graded set is on /learn/.
Count billed rows and providers by Level II letter
DuckDB SQL
SELECT LEFT(hcpcs_code, 1) AS letter, COUNT(*) AS rows, COUNT(DISTINCT npi) AS npi_count FROM provider_services_hi GROUP BY letter ORDER BY rows DESC;Shows why rows and providers differ. Illustrative DuckDB SQL; graded exercises live on /learn/.
Check that every billed code is in the Level II file
DuckDB SQL
SELECT COUNT(*) AS unmatched FROM provider_services_hi s LEFT JOIN hcpcs_level2 h USING (hcpcs_code) WHERE h.hcpcs_code IS NULL;Returns 0 for this site's Hawaii cut. Run this check before trusting any join to a code file.
Most common codes by number of providers, with descriptions
DuckDB SQL
SELECT s.hcpcs_code, h.short_description, COUNT(DISTINCT s.npi) AS npi_count FROM provider_services_hi s JOIN hcpcs_level2 h USING (hcpcs_code) GROUP BY ALL ORDER BY npi_count DESC, s.hcpcs_code LIMIT 10;Where this shows up in the claim cycle: How a Claim Gets Paid, module 2: The claim's journey
Used in: Join to HCPCS Level II descriptions · Which specialties bill drug J codes? · Flu shot administration by specialty