🏛️
CASE #02 · CONFIDENTIAL · ANALYST BRIEFING
Management Fee Policy Decisions
A large insurance company · 4 employers · 819 members · 5 savings products
The Situation
You've joined the business research department at a large insurance company.
Management is weighing decisions about the management fees paid on the savings products
of employees at specific employers. You've been asked to investigate the data and provide business insights.
Management fees = the commission paid to the insurance company from the saver's account.
There are two types: deposit fees (a percentage of the monthly deposit) and accumulation fees
(a percentage of the total accumulation). Both figures are percentages — if it says "2" it means 2%.
4 / 5
employers / products
Your mission
You will go through 13 structured steps that take you from a basic familiarity with the data,
through management-fee calculations and segmentations, to advanced questions with Window Functions
and Subqueries — just like a real business analysis presented to management.
The 9 questions management wants answered
- What are the maximum management fees charged at each employer × fund?
- How many shekels are actually collected from accumulation fees?
- Is there a difference in management fees between men and women?
- The first and last dates from the start and end of each group (window function required)
- Who are the members working for more than one employer?
- How many different products does each such member hold?
- Which product shows the highest total accumulation?
- Which product shows the highest accumulation per single member?
- What are the company's annual revenues from the 2 continuing-education funds?
The Rules
- You can write as many queries as you want in each step.
- A step is marked ✓ automatically when the query is on the right track.
- The sample code in the hint is locked until you run your first query — try it yourself first!
- The 🗄️ Table Viewer button is always available in the header.