e****0
发帖数: 378 | 1 take home assignment里的一道。由于我assigment做挂了，所以没机会知道反馈了。
想知道有什么好的写法。
table:
row_id - A unique id for the row
customer_id — A unique id for the loan borrower
loan_amount — The amount of the loan requested by the borrower
addr_state — Borrower's state of residence
问题：
Now let's rank the states by their top decile of loans to see where
customers borrow the most. Find the states where the cutoff for the top 10%
of loans (by loan amount requested) is the highest. If two states have the
same cut-off for the top decile, then please break ties by preferring the
state with the highest number of loans requested. Provide the list of the
top 10 states ranked in this manner as well as the total number of loans
requested in each state and the loan amount cut-off for the top decile of
loans.
多谢大家！ | A*******e
发帖数: 2419 | 2 没看懂问题。有示例么？
%
【在 e****0 的大作中提到】
: take home assignment里的一道。由于我assigment做挂了，所以没机会知道反馈了。
: 想知道有什么好的写法。
: table:
: row_id - A unique id for the row
: customer_id — A unique id for the loan borrower
: loan_amount — The amount of the loan requested by the borrower
: addr_state — Borrower's state of residence
: 问题：
: Now let's rank the states by their top decile of loans to see where
: customers borrow the most. Find the states where the cutoff for the top 10%
| n*********u
发帖数: 1030 | 3 SELECT state, loan_amount
FROM (
SELECT t1.loan_amount, t1.state, t1.rank, t2.ct
FROM (
SELECT loan_amount, state, (CASE state WHEN @currState THEN @rownum:
= @rownum + 1 ELSE @rownum := 1 AND @currState=state END) + 1 as rank, @
ctnum:=@ctnum+1 ac ct
FROM table t, (SELECT @rownum := 0, @currState := '', @ctnum:=0) r
ORDER BY state, loan_amount DESC
) t1 WHERE t1.rank = t1.ct * 0.1
) tt
order by tt.rank, tt.ct DESC
limit 10; |
|
