ZSCALER Coding Question – Solved

Question

Expected Cash Flow Analysis

A financial analytics platform is developing a feature to help investors understand the expected cash flows from their bond investments. Create a query that provides insights into each investor's expected cash flows.

The output should list all investors with their total number of investments, the sum of expected cash flows, and the range of expected cash flows (the difference between the highest and the lowest expected cash flow).

The result should have the following columns:  
email | investment_count | total_expected_flow | range_expected_flow  
- email: investor email  
- investment_count: total number of investments held by a specific investor  
- total_expected_flow: sum of expected cash flows for a specific investor  
- range_expected_flow: the range of expected cash flows for a specific investor (maximum - minimum)

Only investors who have a sum of expected cash flows greater than 100,000 should be included in the result. Sort the result in ascending order by email.

TreasuresBase Expert · Accepted Answer

SELECT 
    i.email,
    COUNT(c.expected_floe) AS investment_count,
    SUM(c.expected_floe) AS total_expected_flow,
    MAX(c.expected_floe) - MIN(c.expected_floe) AS range_expected_flow
FROM 
    investors i
JOIN 
    cash_flows c ON i.id = c.investor_id
GROUP BY 
    i.email
HAVING 
    SUM(c.expected_floe) > 100000
ORDER BY 
    i.email ASC;

ZSCALER Coding Question – Solved

Image of the Question

All Testcases Passed ✔

Solution

🔒 Please login to view the solution

Explanation

Related Questions