Selecting columns from three tables using nested join

Question

I have created the following tables:

USER TABLE

user_id (primary key)
account_created (date)
email (varchar)
usage_count (number)

PRODUCT TABLE

product_id (primary key)
product (varchar) (values include “iPhone”, “Android”, “Windows”)
users_supported (number)

(users supported notes: some phones can support group calls up to 1000 users, some can only support normal calls of 2 users)

USAGE TABLE

usage_id (primary key)
product_id (foreign key)
user_id (foreign key)
usage_date (date)
purchase_call (number) (can be a 0, 2, 4, 6, or 10 min call)
usage_winnings (number) (when users use their minutes, sometimes they will randomly earn cash back)
computer_usage (binary value) (users can link the phone to a computer, and make calls through their computer, similar to google voice)

PAYMENT TABLE

payment_id (primary key)
user_id (foreign key)
payment_type (char) (either D or W)
 (D to deposit money to purchase minutes, or W to withdraw cash back bonus from usage_winnings)
payment_date (date)
amount (number)

I want to write a select statement that displays the following list:

Total minutes purchased for iPhone in 2016
Total minutes purchased for the other types of phones in 2016
The total amount the user received from usage_winnings EVER
% of calls made on computer for all phones in 2016 (to explain this some more, you can purchase minutes for an iPhone, then make a call with that iPhone through a computer, similar to Google Voice)
The last PAID usage date for a phone call (a 0 minute phone call, or a phone call shorter than a minute is considered free, so only values that include 2, 4, 6, or 10)
The net account balance in the user's account (deposits - withdraws)

With the following constraints: Have made at least one usage (they have some minutes in their account), created their account in 2016, and used an iPhone in 2013.

So far I have:

SELECT u.user_id, SUM(us.purchase_minutes), SUM(us.purchase_minutes), COUNT(us.usage_winnings), COUNT(us.computer_usage) / SELECT COUNT(us.usage_id), us.usage_date, pa.amount
FROM ‘USER’ u, ‘USAGE’ us, ‘PAYMENTS’ pa
INNER JOIN USAGE us ON u.user_id = us.user_id
JOIN PRODUCT p ON us.product_id = p.product_id
JOIN PAYMENT pa ON USER u
WHERE p.product_id = ‘iPhone’
AND u.usage_count > 0
AND u.account_created <= ‘2016-12-31’
AND u.account_created >= ‘2016-01-01’

So far I have the constraints down, but I am unsure how to list the information needed. In the case of point one Total minutes purchased for iPhone in 2016 can I include a WHERE statement following the column name? I.E

SUM(us.purchase_minutes) WHERE p.product = 'iPhone'

I just started learning SQL so please forgive me if this is a basic question. I have asked a related question in this post: How to write a select statement using a nested join

Here is a picture that hopefully paints a more detailed picture of the database https://i.sstatic.net/fsmoR.png

Possible duplicate of How to write a select statement using a nested join — e4c5
– e4c5, Commented Dec 23, 2016 at 3:54
new question new post is very much right but this looks exactly like the old one to me. I would also recommend that you read stackoverflow.com/help/how-to-ask and be aware that we hate sql tabels and sample data being posted as images — e4c5
– e4c5, Commented Dec 23, 2016 at 4:04
what you have so far combines two different styles of joins. very strange would def. give errors. — Hogan
– Hogan, Commented Dec 23, 2016 at 4:08
ogk, that's right; if it's new question then post it separately but instead of posting a long thread, do few things: link the previous related question -> post the table schema as text instead of images -> shorten your issues saying what have been done and what is that you are trying to achieve on top of it. If possible, post some sample data as well. — Rahul
– Rahul, Commented Dec 23, 2016 at 4:10

Hogan · Accepted Answer · 2016-12-23 04:22:56Z

0

This is close -- there might be typos or syntax errors since you don't give some example data and I don't have an easy way to test right now.

But I think this makes it clear what the professor wants you to do -- iron out the details yourself.

SELECT 
  u.user_id, 
  SUM(CASE WHEN p.product = 'iPhone' AND year(payment_date) = 2016 
           THEN p.purchase_call ELSE 0 END) AS q1,
  SUM(CASE WHEN p.product <> 'iPhone' AND year(payment_date) = 2016 
           THEN p.purchase_call ELSE 0 END) as q2,
  SUM(p.usage_winnings) as q3,
  SUM(CASE WHEN p.computer_usage = 0 THEN p.purchase_call ELSE 0 END) /
  SUM(CASE WHEN p.computer_usage = 1 THEN p.purchase_call ELSE 0 END) as q4 ,
  MAX(CASE WHEN p.purchase_call >= 2 THEN pa.payment_date ELSE null END) as q5,
  SUM(CASE WHEN pa.payment_type = 'D' THEN pa.amount ELSE 0 END) -
  SUM(CASE WHEN pa.payment_type = 'W' THEN pa.amount ELSE 0 END) as q6
FROM USER u
JOIN USAGE us ON u.user_id = us.user_id
JOIN PRODUCT p ON us.product_id = p.product_id  
JOIN PAYMENT pa ON u.user_id = pa.user_id
GROUP BY u.user_id

answered Dec 23, 2016 at 4:22

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ogk Over a year ago

Thanks for the suggestion. I'm trying get a better understanding of the syntax, but for year(payment_date) wouldn't I need to specify which table the payment_date comes from. Like this pa.payment_date? And p.purchase_call does not exist in the product table. I switched it to us.purchase_call

ogk Over a year ago

My university's network is down right now, but I'm trying to make the most of the downtime still and get a better understanding of the code. I will test it with real data when the network is back up! @Hogan

Collectives™ on Stack Overflow

Selecting columns from three tables using nested join

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related