The salesperson with the highest sales during the promotion period
This is a database issue for a department store. There are two tables in the database, one is the calendar for store promotion time, and the promotion record table is Promotion:
The other is the annual salesperson sales table, sales record table SalesRecord:
What we need to know now is which salesperson has the highest sales amount in each promotional activity, so that we can give that employee a performance bonus.
Loop promotion calendar table, in each loop, first select all sales records of this promotion from the sales record table, then group them by salesperson and calculate the total sales amount to find the salesperson with the highest sales amount. Record their name in the new table sequence, and that's the answer.
| A | B | |
|---|---|---|
| 1 | =T("Promotion.txt") | =T("SalesRecord.txt") |
| 2 | =create(promo_name,best_sale) | |
| 3 | for A1 | =B1.select(between(sale_date, A3.start_date:A3.end_date)) |
| 4 | =B3.groups(clerk_name; sum(sale_amt):total_amt) | |
| 5 | =B4.maxp@a(total_amt) | |
| 6 | >A2.insert(0,A3.promo_name,B5.(clerk_name).concat@c()) |
https://try.esproc.com/splx?3by
A1 obtains information on each promotional activity, B1 is the sales record table.
A2 establishes a result table sequence and prepares to record the name of each promotional activity and the salesperson with the highest sales revenue.
A3 loops every sales activity.
In the loop body, B3 finds sales data during the promotion period. B4 groups and aggregates these sales data by salesperson and calculates the sales revenue for each person. B5 selects the salesperson with the highest sales revenue, and considering the possibility of multiple salespeople having the same sales revenue, adds the @a option. B6 Adds the results of this promotion to the result table, including the promotion name and the best salesperson.
After the loop ends, the final result can be obtained in A2:
After becoming proficient, the code after A2 can be written as:
=A1.new(promo_name, B1.select(between(sale_date, start_date:end_date)).groups(clerk_name; sum(sale_amt): total_amt).maxp@a(total_amt).(clerk_name).concat@c():best_sale)
Replacing the loop body with a loop function yields the same result.