SQLTeam.com | Weblogs | Forums

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

#1

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

#2

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
#3

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
    )