Finding overlap between two tables, and percentage in SQL

TheDon 0
Viewed 36

I have two tables.

One with customer information (including Zip Code). Second is all zip codes around the States.

I am trying to find two things:

  1. Which zip codes overlap from the customer information table and the entire zip codes table?
  2. The percentage of customers per Zip code - so something that overlaps the two tables to see the ratio of customers in each zip code...

I am having a lot of trouble with this. I don't really know where to start. Can anyone advise on a starting point? I am new to SQL.

My initial thought is to have the main query, then the subquery with some sort of a join. But I am struggling to come up with anything.

Edit: Expected output is a column with the overlapping zip codes, and a column with the percentage of customers in that zip code.

sql
1 Answers

With customers, zipcodes your tables; and code the filed for the zipcode in each table, and id some customer id.

Try:

SELECT DISTINCT zipcodes.code from zipcodes LEFT JOIN customers on customers.code = zipcodes.code

for the overlapping zipcodes

and

WITH us_count as (
    SELECT COUNT(*) from zipcodes
    LEFT JOIN customers ON customers.code = zipcodes.code) 
SELECT customers.code, 100 * COUNT(customers.id)/us_count  FROM zipcodes LEFT JOIN customers ON customers.code = zipcodes.code GROUP BY customers.code
PlainRavioli0
Related