CODEWITHSUNDEEP
mysql
user3076760
user3076760

Reputation: 45

Determine which customer generated the most revenue

I have to determine which customer generated the most total revenue. I've managed to create a query that lists all customers and how much revenue they generated.

SELECT P.FirstName, P.LastName, SUM(BookingFee) AS Revenue
FROM Person P, Customer C, Reservation R
WHERE C.Id = P.Id AND R.AccountNo = C.AccountNo
GROUP BY C.AccountNo
ORDER BY Revenue DESC

By putting this in descending order the customer who generated the most revenue is on top. But I have to have the query produce only one row that contains the highest revenue generating customer. I'm not quite sure how to do this or if I'm on the right track with what I have now.

Any revenue is associated with the BookingFee attribute in the Reservation Table.

Here's the relevant portion of my database and some sample data

DROP DATABASE IF EXISTS flight_reservation_system_2;

CREATE DATABASE flight_reservation_system_2;

USE flight_reservation_system_2;



CREATE TABLE Person (
    Id INTEGER,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Phone CHAR(10) NOT NULL,
    Address VARCHAR(100) NOT NULL,

    City VARCHAR(50) NOT NULL,
    State VARCHAR(50) NOT NULL,
    ZipCode INTEGER NOT NULL,
    PRIMARY KEY (Id),
    CHECK (Id > 0),
    CHECK (Phone > 0),
    CHECK (ZipCode > 0)
);

CREATE TABLE Employee (
    Id INTEGER NOT NULL,
    SSN INTEGER,
    IsManager BOOLEAN NOT NULL,
    StartDate DATE NOT NULL,
    HourlyRate NUMERIC(10 , 2 ) NOT NULL,
    PRIMARY KEY (SSN),
    FOREIGN KEY (Id)
        REFERENCES Person (Id),
    UNIQUE (Id),
    CHECK (SSN > 0),
    CHECK (HourlyRate > 0)
);

CREATE TABLE Customer (
    Id INTEGER NOT NULL,
    AccountNo INTEGER,
    CreditCardNo CHAR(16),
    Email VARCHAR(50),
    CreationDate DATETIME NOT NULL,
    Rating INTEGER,
    PRIMARY KEY (AccountNo),
    FOREIGN KEY (Id)
        REFERENCES Person (Id),
    CHECK (Rating >= 0 AND Rating <= 10)
);

CREATE TABLE Reservation (
    ResrNo INTEGER,
    ResrDate DATETIME NOT NULL,
    BookingFee NUMERIC(10 , 2 ) NOT NULL,
    TotalFare NUMERIC(10 , 2 ) NOT NULL,
    RepSSN INTEGER,
    AccountNo INTEGER NOT NULL,
    PRIMARY KEY (ResrNo),
    FOREIGN KEY (RepSSN)
        REFERENCES Employee (SSN),
    FOREIGN KEY (AccountNo)
        REFERENCES Customer (AccountNo),
    CHECK (ResrNo > 0),
    CHECK (BookingFee >= 0),
    CHECK (TotalFare > BookingFee)
);


INSERT INTO Person(Id, FirstName, LastName, Phone, Address, City, State, ZipCode)
VALUES(1, 'John', 'Doe','1231231234', '123 N Fake Street', 'New York', 'New York', 10001),
(2, 'Jane', 'Smith','5555555555', '100 Nicolls Rd','Stony Brook', 'New York', 17790),
(3, 'Rick', 'Astley','3141592653', '1337 Internet Lane', 'Los Angeles', 'California', 90001);

INSERT INTO Customer(Id, AccountNo, CreditCardNo, Email, CreationDate, Rating)
VALUES(1, 2, NULL, '[email protected]', '2011-01-01 19:30:00', 8),
(2,  1, NULL, '[email protected]', '2011-01-01 19:30:00', 5),
(3,3, NULL, '[email protected]', '2011-01-01 19:30:00', 2);

INSERT INTO Reservation(ResrNo, ResrDate, BookingFee, TotalFare, RepSSN, AccountNo)
VALUES(111 , '2010-01-03 19:30:00', 10, 1200.00, NULL, 1),
(222 , '2011-01-03 19:30:00', 30, 500.00,NULL, 2),
(333 , '2011-01-03 19:30:00', 25, 3333.33, NULL, 3);

Upvotes: 1

Views: 3762

Answers (1)

Raging Bull
Raging Bull

Reputation: 18757

Use LIMIT:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments.

SELECT P.FirstName, P.LastName, SUM(BookingFee) AS Revenue
FROM Person P, Customer C, Reservation R
WHERE C.Id = P.Id AND R.AccountNo = C.AccountNo
GROUP BY C.AccountNo
ORDER BY Revenue DESC
LIMIT 1

Result:

FIRSTNAME   LASTNAME    REVENUE
John        Doe         30

See result in SQL Fiddle.

Upvotes: 1

Related Questions