none
Listing Top 5 customers RRS feed

  • Question

  • I have a question. Can you please tell me how to list top five customers in terms of the total amount of money they have spent in a particular database and table?
    Wednesday, May 20, 2015 3:09 PM

Answers

  • --rank
    
    declare @test table (
    customerid int,
    amount_spent int
    )
    
    insert into @test values(1,100)
    insert into @test values(2,200)
    insert into @test values(51,102)
    insert into @test values(321,300)
    insert into @test values(4,50)
    insert into @test values(11,500)
    insert into @test values(12,100)
    insert into @test values(19,100)
    insert into @test values(81,432)
    insert into @test values(51,500)
    
    ;with cte as (
    Select *,RANK() over (order by amount_spent desc) rnk  from @test
    )
    Select * from cte
    where rnk<=5
    
    --dense_rank()
    
    declare @test table (
    customerid int,
    amount_spent int
    )
    
    insert into @test values(1,100)
    insert into @test values(2,200)
    insert into @test values(51,102)
    insert into @test values(321,300)
    insert into @test values(4,50)
    insert into @test values(11,500)
    insert into @test values(12,100)
    insert into @test values(19,100)
    insert into @test values(81,432)
    insert into @test values(51,500)
    
    ;with cte as (
    Select *,dense_rank() over (order by amount_spent desc) rnk  from @test
    )
    --Select *,RANK() over (order by amount_spent ) rnk from cte;
    --Select *,row_number() over (order by amount_spent desc) rnk from cte;
    Select * from cte
    where rnk<=5
    


    - please mark correct answers

    • Marked as answer by KS2909 Wednesday, May 20, 2015 5:07 PM
    Wednesday, May 20, 2015 3:46 PM

All replies

  • Please provide details of your table structure (DDL) and some sample data (DML).

    Thanks, Bharath bharath-msbi.blogspot.com

    Wednesday, May 20, 2015 3:36 PM
  • You can assume any database of your choice. I just want to know the generic sql code for listing top 5 customer and the total amount of money they have spent.
    Wednesday, May 20, 2015 3:41 PM
  • --rank
    
    declare @test table (
    customerid int,
    amount_spent int
    )
    
    insert into @test values(1,100)
    insert into @test values(2,200)
    insert into @test values(51,102)
    insert into @test values(321,300)
    insert into @test values(4,50)
    insert into @test values(11,500)
    insert into @test values(12,100)
    insert into @test values(19,100)
    insert into @test values(81,432)
    insert into @test values(51,500)
    
    ;with cte as (
    Select *,RANK() over (order by amount_spent desc) rnk  from @test
    )
    Select * from cte
    where rnk<=5
    
    --dense_rank()
    
    declare @test table (
    customerid int,
    amount_spent int
    )
    
    insert into @test values(1,100)
    insert into @test values(2,200)
    insert into @test values(51,102)
    insert into @test values(321,300)
    insert into @test values(4,50)
    insert into @test values(11,500)
    insert into @test values(12,100)
    insert into @test values(19,100)
    insert into @test values(81,432)
    insert into @test values(51,500)
    
    ;with cte as (
    Select *,dense_rank() over (order by amount_spent desc) rnk  from @test
    )
    --Select *,RANK() over (order by amount_spent ) rnk from cte;
    --Select *,row_number() over (order by amount_spent desc) rnk from cte;
    Select * from cte
    where rnk<=5
    


    - please mark correct answers

    • Marked as answer by KS2909 Wednesday, May 20, 2015 5:07 PM
    Wednesday, May 20, 2015 3:46 PM
  • SELECT TOP 5 CUSTOMER_NAME FROM CUSTOMER ORDER BY MONEY_SPENT DESC


    Thanks, Bharath bharath-msbi.blogspot.com

    Wednesday, May 20, 2015 3:46 PM
  • You can assume any database of your choice. I just want to know the generic sql code for listing top 5 customer and the total amount of money they have spent.

    Not really, no.

    Does your customer live in the orders table? The products he bought in the orderItems table? The price for those products in the items table? Does all the information reside in a single table? Are your orders broken down by product? 

    Assuming all the information lives in a single table:

    SELECT TOP 5 customerID, SUM(orderValue) AS orderValue
      FROM orders
     GROUP BY customerID
     ORDER BY orderValue DESC

    There you have it. The simple, generic, top 5.

    In practicality it's useless. 

    Your DDL is nothing that has not been done before, it's not so super secret that you cannot show it to us.

    If you want a real answer to your question, post the DDL and example data.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.


    • Edited by Patrick Hurst Wednesday, May 20, 2015 3:51 PM
    • Proposed as answer by Stan210 Wednesday, May 20, 2015 4:31 PM
    Wednesday, May 20, 2015 3:50 PM
    1.       List all customers (first and last name) that have never ordered anything

    1.       Write a query to get customer Name, Age and “Age Category”.  “Age Category” will be a new column and should be set based on age per the conditions below:
      • If Age is less than 18, it should be set to ‘Minor’
      • If Age is between 18 and 64 set it to ‘Adult’
      • If Age is more than 64 set it to ‘Senior’
    1.       List the last name, first name, and order date of all customers that ordered items between the month February and September of the current year

                                                                                                      

    1.       Write a query to update the quantity ordered from ‘1’ to ‘5’ for customer ‘John Smith’ with the order date of June 30, 1999 and for the item named ‘Toy Truck’
    1.       List all customers who ordered items last year but not this year
    1.       Show the total sales by item name and location
    Wednesday, May 20, 2015 4:38 PM
  • You can assume any database of your choice. I just want to know the generic sql code for listing top 5 customer and the total amount of money they have spent.

    Not really, no.

    Does your customer live in the orders table? The products he bought in the orderItems table? The price for those products in the items table? Does all the information reside in a single table? Are your orders broken down by product? 

    Assuming all the information lives in a single table:

    SELECT TOP 5 customerID, SUM(orderValue) AS orderValue
      FROM orders
     GROUP BY customerID
     ORDER BY orderValue DESC

    There you have it. The simple, generic, top 5.

    In practicality it's useless. 

    Your DDL is nothing that has not been done before, it's not so super secret that you cannot show it to us.

    If you want a real answer to your question, post the DDL and example data.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.


    Thank a lot! its really helpful post.
    Wednesday, May 20, 2015 4:54 PM
    1.       List all customers (first and last name) that have never ordered anything

    1.       Write a query to get customer Name, Age and “Age Category”.  “Age Category” will be a new column and should be set based on age per the conditions below:
      • If Age is less than 18, it should be set to ‘Minor’
      • If Age is between 18 and 64 set it to ‘Adult’
      • If Age is more than 64 set it to ‘Senior’
    1.       List the last name, first name, and order date of all customers that ordered items between the month February and September of the current year

                                                                                                      

    1.       Write a query to update the quantity ordered from ‘1’ to ‘5’ for customer ‘John Smith’ with the order date of June 30, 1999 and for the item named ‘Toy Truck’
    1.       List all customers who ordered items last year but not this year
    1.       Show the total sales by item name and location
    Please understand that this is not a forum to give answers to your assignments without any try or effort from your side. Hope from next time you will try to put some effort before asking for help.

    - please mark correct answers

    Wednesday, May 20, 2015 5:26 PM
  • @KS2909, before posting this big list of your homework go through some guidelines on posting question on this forum:

    POSTING TIPS - Code, Images, Hyperlinks, Details


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page

    Wednesday, May 20, 2015 6:01 PM
  • Where is the DDL? How do you  program without specs, the name of the table, DDL, keys, etc? Magic? Mind reading? 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, May 20, 2015 7:16 PM