Understanding the Problem Statement

The given question revolves around a SQL query that needs to be written to count the number of accounts based on certain criteria. The criteria are:

• Accounts that received letter only
• Accounts that received email only
• Accounts that received both letters and emails

To solve this problem, we need to understand how to use SQL joins, subqueries, and group by clauses.

Understanding the Table Structure

The table structure is as follows:

The table has two columns: ACCOUNT_ID and TYPE_OF_COMM. The first column stores the account IDs, and the second column stores the type of communication received by each account.

Using SQL Joins to Solve the Problem

To solve this problem, we can use SQL joins to combine rows from different tables based on a related column between them. In this case, we have two tables: TABLE1 (with ACCOUNT_ID and TYPE_OF_COMM) and TABLE2 (which is not shown in the original question). However, since the table does not exist, we will assume that there are two columns in TABLE1: ACCOUNT_ID and TYPE_OF_COMM.

We can use a LEFT JOIN to combine rows from TABLE1 with a subquery that checks if the account has received only one type of communication or both types. Here’s how you could implement it:

-- Create a temporary view for easy reference
with comm_type as (
    select distinct t1.ACCOUNT_ID 
        , case
            when t1.TYPE_OF_COMM like 'Email' and t2.TYPE_OF_COMM is null then 'Email Only'
            when t1.TYPE_OF_COMM like 'Letter' and t2.TYPE_OF_COMM is null then 'Letter Only'
            when t1.TYPE_OF_COMM is not null and t2.TYPE_OF_COMM is not null then 'Both Letters and Mails'
            else 'Unkown'
          end TYPE_OF_COMMS
    from tbl t1
    left join tbl t2
        on t1.ACCOUNT_ID = t2.ACCOUNT_ID
        and t1.TYPE_OF_COMM <> t2.TYPE_OF_COMM
)
-- Select the required columns and count the number of accounts for each type
select TYPE_OF_COMMS 
    , count(distinct ACCOUNT_ID)
from comm_type
group by TYPE_OF_COMMS

Explanation

In this solution, we first create a temporary view (CTE) that combines rows from TABLE1 with a subquery. The subquery checks if the account has received only one type of communication or both types.

We use the LEFT JOIN keyword to combine rows from TABLE1 with the subquery. This allows us to check for accounts that have not been matched in the subquery (i.e., those that receive two types of communications).

The CASE statement is used to assign a value to TYPE_OF_COMMS based on the type of communication received by each account.

Once we’ve created the temporary view, we can select the required columns and count the number of accounts for each type using the GROUP BY clause.

Alternative Solution Using Common Table Expressions

Another way to solve this problem is by using a Common Table Expression (CTE). Here’s how you could implement it:

-- Create a CTE for easy reference
with comm_type as (
    select distinct t1.ACCOUNT_ID 
        , case
            when t1.TYPE_OF_COMM like 'Email' and t2.TYPE_OF_COMM is null then 'Email Only'
            when t1.TYPE_OF_COMM like 'Letter' and t2.TYPE_OF_COMM is null then 'Letter Only'
            when t1.TYPE_OF_COMM is not null and t2.TYPE_OF_COMM is not null then 'Both Letters and Mails'
            else 'Unkown'
          end TYPE_OF_COMMS
    from tbl t1
    left join tbl t2
        on t1.ACCOUNT_ID = t2.ACCOUNT_ID
        and t1.TYPE_OF_COMM <> t2.TYPE_OF_COMM
)
-- Select the required columns and count the number of accounts for each type
select TYPE_OF_COMMS 
    , count(distinct ACCOUNT_ID) as COUNT_OF_ACCOUNTS
from comm_type
group by TYPE_OF_COMMS

Explanation

In this solution, we create a CTE (Common Table Expression) that combines rows from TABLE1 with the subquery.

The rest of the logic is similar to the previous solution. We use the LEFT JOIN keyword to combine rows from TABLE1 with the subquery, and then select the required columns and count the number of accounts for each type using the GROUP BY clause.

Conclusion

In this blog post, we discussed how to write a SQL query that counts the number of accounts based on certain criteria. We used SQL joins, subqueries, and group by clauses to solve the problem.

We also explored alternative solutions using Common Table Expressions (CTEs).

I hope this helps you understand how to solve similar problems in the future. Happy coding!

Last modified on 2023-09-24