Number of Trades

Question

Number of Trades

dangelo211

SSC Enthusiast

Points: 160
More actions
March 21, 2018 at 4:29 pm

#403193

I need to write a query to extract the region of the trader, his name & the number of trades he/she did
Please let me know if this is correct

Select top 10 TraderID, NameofTrader, RegionofTrader, Ticker, TraderDate, Count(*) NumTrades
from Trading
left join on user User.TraderID = Trading.TraderID
Group by TraderID, NameofTrader, RegionofTrader, Ticker, TraderDate
order by TraderDate desc

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

dangelo211 SSC Enthusiast Points: 160 More actions · Answer 1

Select U.TraderID, U.NameofTrader, U.RegionofTrader, Count(*) NumTrades
from Trading T
left join on user U U.TraderID = T.TraderID
Group by U.TraderID, U.NameofTrader, U.RegionofTrader
order by count(*) desc

or does this answer the question
what is the query to pull the region of the trader, his name & the number of trades he/she did

x SSC-Insane Points: 23660 More actions · Answer 2

dangelo211 - Wednesday, March 21, 2018 7:35 PM

Select U.TraderID, U.NameofTrader, U.RegionofTrader, Count(*) NumTrades
from Trading T
left join on user U U.TraderID = T.TraderID
Group by U.TraderID, U.NameofTrader, U.RegionofTrader
order by count(*) desc
or does this answer the question
what is the query to pull the region of the trader, his name & the number of trades he/she did

The key word "on" that applies to the "join" clause is in the wrong place, do you want to fix that first ?

dangelo211 SSC Enthusiast Points: 160 More actions · Answer 3

OOPs did not see that ... Corrected code Thank you.. Please let me know

SELECT User.TraderID, User.NameofTrader, User.RegionofTrader, Trade.Ticker, Trade.TraderDate, Count(*) AS NumTrades
FROM [User] INNER JOIN Trade ON User.TraderID = Trade.[Trader ID]
GROUP BY User.TraderID, User.NameofTrader, User.RegionofTrader, Trade.Ticker, Trade.TraderDate;
order by count(*) desc

SELECT User.TraderID, User.NameofTrader, User.RegionofTrader, count(*) AS NumTrades
FROM [User] INNER JOIN Trade ON User.TraderID = Trade.[Trader ID]
GROUP BY User.TraderID, User.NameofTrader, User.RegionofTrader
ORDER BY count(*) DESC;

x SSC-Insane Points: 23660 More actions · Answer 4

seems ok, without seeing tables and data its hard to tell in absolute certainty. What I often do to build confidence in my queries, especially with these that aggregates data in groups, is to verify a few groups in isolation with simpler queries that should be obviously correct. I've done this often and its saved me some embarrassment that would happen if I shipped the malfunctioning query.

Like pick just one region and trader and verify that the number of trades matches whats calculated in your grouping query for the same criteria, and repeat as many times as you can imagine, especially if you can find some special cases that might be tricky. Also if theres any nulls anywhere watch for those cases, especially since you're left joining to the users.

I can certainly understand your concerns about accuracy, bugs and misunderstandings can often come out of left field and I've caught out a few mistakes from even the most confident of query writers, including me LOL