Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012

    Unanswered: Please provide sql query

    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. 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. #2
    Join Date
    Apr 2002
    Toronto, Canada
    desperate sql student is desperate
    homework is due and she hasn't a clue | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Provided Answers: 10
    What have you tried so far?

    We don't do homework here but if you give it a go yourself then we can maybe provide some feedback
    Home | Blog

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts