SQLTeam.com | Weblogs | Forums

Trying to learn SQL and need help writing a super easy query

This is probably the easiest query you'll ever have to write, but I'm not quite sure how to wrap my head around this yet. I know some basics with SQL, but haven't even modified a query in years. I'm hoping for help so I can digest how this type of a request would work and would love some insight. Based on the below data, I'm thinking the results will be $0, but would love to see a query to help me get a better grasp of this problem. #1 is the request and I've attached a photo of the table(s) I'm working from. This is where I've gotten so far...

Blockquote

SELECT SUM(Account_MRR)

FROM Accounts

JOIN Success

ON ID = AccountID

Where Type IS NOT NULL

Blockquote

Thank you SO much in advance for any and all help.

  1. Given the sample tables below, write a SQL query to show the combined Account MRR by Region for active contracts of at least 6 months with at least one Success engagement.

SQL Query

Pictures are good to show what you want, but supplying DDL and sample data is always easier for us to help with.

drop table if exists #accounts
drop table if exists #Subscription
drop table if exists #Success


Create table #Accounts (id int, 
						Name varchar(10),
						Account_MRR numeric (6,2),
						Region  varchar(10))

Create table #Subscription (id int,
							AccountID int,
							ContractLength tinyint,
							Subscr numeric(6,2),
							Status varchar(10))

Create table #Success (id int,
					   AccountID int,
					   Type varchar(10))

insert into #Accounts (id, Name, Account_MRR, Region) values (100005,'Acme',1500,'EMEA')
insert into #Subscription (id, AccountID, ContractLength, Subscr, Status) values (80003,100005, 3, 1000, 'Active')
insert into #Success(id, AccountID, Type) values (60004,100005, 'Services')



select a.Region, Sum(a.account_mrr)
  from #Accounts a
	join #Success s
		on a.id = s.AccountID
	join #Subscription sb
		on a.id = sb.AccountID
		and sb.Status = 'Active'
		and sb.ContractLength >= 6
group by a.Region

The tricky part here is the #Success table lookup / verification. I don't believe you can directly JOIN to it because there may be many of them, which would over-inflate the SUM(). Instead, you need to use EXISTS() to verify that at least one exists, as stated in your requirements for this query.

SELECT a.Region, SUM(a.Account_MRR) AS Combined_Account_MRR
FROM #Accounts a
INNER JOIN #Subscription s ON 
    s.AccountID = a.ID AND
    s.ContractLength >= 6 AND
    s.Status = 'Active'
WHERE EXISTS(
    SELECT 1 
    FROM #Success s
    WHERE s.AccountID = a.ID
    )