Top n Clients Accounting for Half of the Total Sales in This Year

Problem

The table below is the historic sales contracts of an enterprise:

The table below is the Client table:

Clients ranked by sales value in a certain year. The top n clients accounting for half of the total sales value is called as Key account. Please list the key account of this enterprise in the year of 1998.

Tip

Rough train of thought: Firstly, group the Contract table by Client, compute the total sales value achieved by each client and arrange the results in descending order, then work out the half value of the total sales value. Finally, scan this table and aggregate sales value during this process till reaching half of the total sales value. Then, the a few top clients will be the key accounts.

1. In order to facilitate the client name retrieval, update the Client field in the Contract table to the corresponding Client records.
2. Select out the Contract record in the 1998
3. Group by Client for the screened table. Then, the sales contract of each client will be grouped together, and a new table sequence will be generated. Please sum up the amount of each group.
4. Sort this table sequence in descending order
5. Compute the total sales volume of all clients, that is, half of the total annual sales value.
6. Traverse every record top-down with pselect function, and sum up the amount till the amount reaches half of the total sum. Once traverse stopped, you will get the number of key accounts.
7. Retrieve the name of top n clients, and these clients are the key account of this year.

Code

Result