0

I'm trying to understand this query statement.

write a query to show % of total profit that the top 10% of businesses generated.

1st : get total profit from all businesses

2nd : get % of all profits generated by very single business

3rd : sort that profit % by desc

4th : get top 10

that's what I've understood.

But somewhere am thinking its not correct what I've thought.

Can anyone please suggest what could be actual ask of this query ?

Note: cant ask back from where I got, because this was in interview :)

Thanks

Oxana Grey
  • 327
  • 2
  • 12
  • It would be better to ask an author, because I suspect there are no users who can read someone's mind in the past. – astentx Jan 27 '22 at 12:56
  • These questions are similar: https://stackoverflow.com/questions/38267026/sql-db2-query-to-get-count-of-top-10-revenue-contributing-customers https://stackoverflow.com/questions/52262798/sql-how-to-find-top-customers-that-pay-80-of-revenue –  Jan 27 '22 at 12:57
  • @astentx, true, but that's not possible as of now. would be great if you could share what you think if this statement ? :) – Oxana Grey Jan 27 '22 at 12:58
  • @KrzysztofSkrzypski, thanks for sharing, but am struggling to understand it. what does it mean <> ? I think its same applicable to my statement too – Oxana Grey Jan 27 '22 at 13:00

1 Answers1

1
  1. Order the businesses by amount of profit.
  2. Take the top 10% of the total number of businesses (with or without ties).
  3. Calculate the total profit for that 10% of businesses.
  4. Compare it to the total profit for 100% of the businesses.

You can use the PERCENT_RANK analytic function to give a percentage rank to the businesses in order of profit and then use conditional aggregation to sum the profit for those businesses with the top 10% profit and compare it to the total profit to get the percentage profit made by the top 10% of businesses:

SELECT SUM(CASE WHEN pct_rnk <= 0.1 THEN profit END)
       / SUM(profit) * 100 AS percent_profit_for_top_10_percent
FROM   (
  SELECT profit,
         PERCENT_RANK() OVER (ORDER BY profit DESC) AS pct_rnk
  FROM   businesses
)

You can also use:

SELECT (SELECT SUM(profit)
        FROM   (
          SELECT profit
          FROM   businesses
          ORDER BY profit DESC
          FETCH FIRST 10 PERCENT ROWS WITH TIES
        )
       )
       /
       (SELECT SUM(profit)
        FROM   businesses)
       * 100 AS percent_profit_for_top_10_percent
FROM   DUAL;

Which, for the sample data:

CREATE TABLE businesses (profit) AS
SELECT  1000 FROM DUAL CONNECT BY LEVEL <= 10 UNION ALL
SELECT  2000 FROM DUAL CONNECT BY LEVEL <= 8 UNION ALL
SELECT  3000 FROM DUAL CONNECT BY LEVEL <= 6 UNION ALL
SELECT  4000 FROM DUAL CONNECT BY LEVEL <= 4 UNION ALL
SELECT  5000 FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
SELECT 10000 FROM DUAL CONNECT BY LEVEL <= 1;

Both output:

PERCENT_PROFIT_FOR_TOP_10_PERCENT
45

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117