Get count of contacts with email addresses that matches other contacts in same tables

Question

I have a contacts table that is like this:

|id|first_name|last_name|date_entered|

then, I have a table that stores the email addresses like this:

|id|email_address|

I then have a relational table that joins the two tables above together like this:

|id|contact_id|email_id|

What I am trying to figure out is, how do I get a count of all new contacts that has been entered in between 2023-02-10 and 2023-02-13 but only the contacts that have matching emails?

For example, a new contact is entered in the db with the email of [email protected]. A few days later, that same contact comes back and is entered in by another person but with the same email address of [email protected].

I am building a report of "New vs Returning Customers" and we use their email to identify a customer that is returning. So, I am needing to be able to list a count of all customers in the database with the same email that is > 1. Is this possible with MySQL? Thanks!

Answer 1

Perhaps something like this

SELECT C.first_name,C.last_name,CE.contact_id,E.email_address
FROM contacts_emails CE
INNER contacts C ON CE.contact_id = C.id
INNER JOIN emails E ON CE.email_id = E.id
WHERE C.date_entered >= '2023-02-10 00:00:00'
AND   C.date_entered <= '2023-02-13 23:59:59';

Now to identify new verses returning

SELECT
    C.first_name
   ,C.last_name
   ,CE.contact_id
   ,E.email_address
   ,IF(EC.emailcnt=1,'New','Returning') NewOrReturning
FROM contacts_emails CE
INNER contacts C ON CE.contact_id = C.id
INNER JOIN emails E ON CE.email_id = E.id
INNER JOIN
(
    SELECT email_address,COUNT(1) emailcnt
    FROM emails GROUP BY email_address
) EC ON E.email_address = EC.email_address
WHERE C.date_entered >= '2023-02-10 00:00:00'
AND   C.date_entered <= '2023-02-13 23:59:59';

This is just a start. Hope this helps.