Find which customer has generated the most revenues for each store. tim category

Question

Find which customer has generated the most revenues for each store.

tim category rental langusge d TINYINT3) | o name CHAR(20) rentLid INT 11) rental-date DATETIME ? category-id TINYINT(3) | imventory-id MEDIUMINT(8) inventary id MEDIUMINT(B) return_date DATETIME last updste TIMESTAMP last update TIMESTAMP customer_id SMALLINT 5) category_id TINYINT3) m_id SMALLINT(5) name VARCHAR 25 latupdste TIMESTAMPdescripian TEXT ttle VARCHAR(255) -Hxrd TINYINT 3) frst sme VARCHAR (45) asname VARCHAR(45) email VARCHAR(50) release-year YEAR languago TINYINTG) ·eddremjd SMALLINT(5) eddresjd SMALLINT(5) ·customer-id SMALLINT(5) active TINYINT(1) crestedate DATETIME rentrste DECIMAL(42) - rent id INT(11) last update TIMESTAMP replacement cast DECIMAL 5,2) Payment-date DATETIME lastupdate TIMESTAMP | | frstname VARCHAR(45) | rating ENUM(..) last_name VARCHAR(45) | | ecial-features SET( ) last update TIMESTAMPlest update TIMESTAMP TINYINT(a) rtname VARCHAR(45) addresid SMALLINT(5) tilm_actor actor_id SMALLINT(5) m_id SMALLINT(5) tilm text | m_id SMALLINT(6) ttle VARCHAR(255) descriptan TEXT addre_jid SMALLINT 5) eddre VARCHAR(50) "2 VARCHAR(50) email VARCHAR(50) +-- Hsdde active TINYINT(1) dstrid VARCHAR(20) country last updste TIMESTAMP uername VARCHAR(16) country_id SMALLINT 5) country VARCHAR(50) last updaste TIMESTAMP dy VARCHAR(50) pasward VARCHAR(40) lastupdste TIMESTAMP phone VARCHAR(20) last updste TIMESTAMP s update TtMESTAMP

Explanation / Answer

Query

select customer.customer_id. first_name, last_name, max(amount) as highest_revenue
from customer
inner join payment on customer.customer_id = payment.customer_id
inner join store on customer.store_id = store.store_id;

Explanation

It select the customer_id, first_name and last_name from the customer table, and select the highest amount from the payment table by using the aggregate function max() with joining the table payment table where both the customer_id are equal and then again inner join with the store table for checking the store_id from the table customer and the store and then display the (row) or details of the customer by satisfying all the conditions.
  

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.