🏛️
CASE #02 · CONFIDENTIAL · ANALYST BRIEFING
Management fee policy decisions
A large insurance company · 4 employers · 819 members · 5 savings products
The situation
You joined the business research department atA large insurance company.
Management is struggling to make decisions about The management fees paid for the savings products
of employees at specific employers. You've been asked to investigate the data and provide business insights.
Management fee = the commission paid to the insurance company from the saver's account.
There are two types: Management fee from deposit (percentage of the monthly deposit) andManagement fee from accumulation
(percentage of the total balance). The two sums in percentages — if it says "2" it means 2%.
4 / 5
Employers / Products
Your task
will pass 13 built-in steps that will take you from a basic introduction to the data,
through management-fee calculations and segmentations, to advanced questions with Window Functions
and Subqueries — just like real business analysis presented to management.
The 9 questions management wants answers to
- which Maximum management fees collected for each employer × fund?
- how many ILS in practice collected from management fees on the balance?
- is there Difference in management fees between men and women?
- The first and last dates from the start and end of each group (Requires a window function)
- who Employees who work for more than one employer?
- How many different products Every such colleague holds?
- Which product shows the The highest aggregation in total?
- Which product shows the highest aggregation For a single colleague?
- what The annual revenues of the company from 2 training products?
The rules of the game
- You can write As many queries as you want at every step.
- Step marked ✓ Automatic When the query is aimed in the right direction.
- The sample code in the hint locked until you run the first query — try it yourself first!
- button 🗄️ Overview of the tables Always available in the header.