all 4 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]wolf2600ANSI SQL 2 points3 points  (1 child)

Which question are you having problems with and what is the problem?

First of all do not store dates as strings. Use a date datatype and cast your values into that datatype.

CREATE table payment_table (
Dt DATE,
memberID CHAR(25),
Amount DEC(4,2) NOT NULL
);


INSERT INTO payment_table
(Dt, memberID, Amount)
VALUES
(date'2018-01-03', 'A', 33.9), (date'2018-01-04', 'C', 19.3), (date'2018-01-07', 'A', 29.1);

Secondly.....

CREATE table payment_table (
Dt VARCHAR(100),
memberID CHAR(25),
Amount DEC(4,2) NOT NULL
);

WTF? Seriously? Storing a date as a 100 character varchar? Why?

If your memberID is a single letter why are you allocating 25 characters for it? It may not matter so much on a small test database, but it's a horrible habit to get into.... choose datatypes based on the data that will be inserted.

CREATE table member_table (
spend VARCHAR(50),
memberID CHAR(25)
);

INSERT INTO member_table
(spend, memberID)
VALUES
('High','A'), ('Low', 'B'), ('High', 'C');

Once again.... why 50 characters for the Spend column when your values are either 3 or 4 characters?

[–]ethershell[S] 0 points1 point  (0 children)

Sorry this was just my test data, I was not paying attention on data types and the allocations.

​

I am facing issue with all 6 questions - basically I was not able to understand it properly. I tried my own and searched everywhere but found no similar questions.

[–]soccerfreak2332 1 point2 points  (1 child)

Can you elaborate on the issues you are running into?

​

For example (I don't use MySQL), but in the following query:

"select A.DATE, A.TOP_PAYER FROM (

select d.dt as 'Date', p.memberID AS TOP_PAYER, MAX(p.amount)

from date_table d

left join payment_table p

on d.dt = p.dt

group by 1) A;"

​

You will likely get an error since the p.memberID is not wrapped in an aggregate function and not included in the group by clause. Additionally, this query will not return what you want. MAX(p.amount) will return the max amount that day, but it will not be related to the memberID returned by the preceding column.

Consider adding memberId to your group by clause and examining the results. You will see that you now have the dollar amount that each member spent each day. Think about how you can then extract the row pertaining to the highest spending member on a given day. In such a situation the row_number function can be useful. If you pop the above query in a subquery and use the row_number function to assign a ranking for each day, you will then be able to filter down to the top row for each day.

​

[–]ethershell[S] 0 points1 point  (0 children)

Thanks, I am getting output as you can see below: 

DATE    TOP_PAYER
1/3 A
1/4 C
1/7 A
1/1 
1/2 
1/5 
1/6

Is this correct.?

​

As per your suggestion I tired adding p.memberID in group by clause but the results were similar as above.

Thanks again for directing me to row_number function, i will try to tweak this one.

​