# Thread: Random complicated sql queryies

1. Registered User
Join Date
Feb 2012
Posts
5

## Unanswered: Random complicated sql queryies

Percentiles. Table 3-12 shows a list of customers and their revenue. The list is sorted by revenue in each Geo. The objective is to extract information about the 30th percentile of customers as shown in Table 3-13. There are two ways to define the 30th percentile: 1) By looking at the 30th percentile on a revenue-weighted basis 2) By looking at the 30th percentile on an ordinal basis (each customer counts as 1). For example in AME, Exxon has 40% of the total revenue, so it is also the 30th percentile customer on a revenue weighted basis. However, on an ordinal basis, there are 4 customers, so JP Chase (25%-50%) is the Ordinal customer. To get a better indication of the hypothetical revenue at the 30th percentile, both the ‘Interpolated Customer Revenue at revenue weighted threshold’ and the ‘Interpolated ordinal revenue’ are calculated by using a weighted average of the revenues. For example, for the Interpolated ordinal revenue for AME, we have Exxon at the 25th percentile and JP Chase at the 50th percentile. Since the target is 30th percentile, we weight the revenue from Exxon by 80% and the revenue from JP Chase by 20% to get 920.

Table 3-12:
Customer Revenue

Geo Customer Revenue
AME Exxon 1000
AME JP Chase 600
AME Citigroup 500
AME GE 400
EMEA Deutsche Bank 400
EMEA Vodafone 350
EMEA HSBC 325
EMEA Telecom Italia 300

Table 3-13:
Interpolated threshold for top 30% of customers

Geo Revenue weighted Threshold Customer Interpolated Customer Revenue at revenue weighted threshold Ordinal customer Interpolated ordinal revenue
AME Exxon 1000 JP Chase 920
EMEA Vodafone 398.2143 Vodafone 390

2. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
I thought the question was same as in Microsoft SQL Server forum.
Originally Posted by sqlprogrammer1
Give a sql query to get table 3-13 from table 3-12

Percentiles. Table 3-12 shows a list of customers and their revenue. The list is sorted by revenue in each Geo. ...
...

3. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
desperate sql student is desperate
homework is due and she hasn't a clue