mysql - sql tables to find top 5 male and top 5 female purchase using data stored in two different tables and gender in one table and purchse in another table

Question

CREATE TABLE mutual_fund (
    transaction_id INTEGER(40),
    customer_id INTEGER(40),
    transaction_type ENUM('Purchase','Sale'),
    nav_value INTEGER(40),
    no_of_units INTEGER(40),
    transaction_time TIMESTAMP,
    transaction_status ENUM('Success','Failed','Pending')
);

INSERT INTO mutual_fund (transaction_id,customer_id,transaction_type,nav_value,no_of_units,transaction_time,transaction_status) 
VALUES (1200,11,'Sale',3000,13,'2019-04-01 12:28:05','Success'),
        (1201,12,'Purchase',6000,6,'2019-04-17 14:54:10','Failed'),
        (1202,13,'Sale',2000,20,'2019-01-19 16:41:12','Failed'),
        (1203,14,'Purchase',3400,11,'2019-01-27 20:08:45','Success'),
        (1204,15,'Sale',7000,5,'2019-04-27 14:38:45','Success'),
        (1205,16,'Purchase',1000,10,'2019-04-01 09:28:55','Success'),
        (1206,17,'Sale',20000,12,'2019-01-29 19:01:32','Success'),
        (1207,18,'Purchase',8000,5,'2021-01-26 11:57:02','Success'),
        (1208,19,'Purchase',10000,3,'2021-01-26 12:34:53','Success'),
        (1209,20,'Purchase',9000,9,'2021-04-26 16:13:18','Success'),
        (1210,21,'Sale',19000,9,'2021-04-26 11:03:19','Failed');
CREATE TABLE customer_details (
    customer_id INTEGER(40),
    customer_name CHARACTER VARYING(40),
    customer_PAN CHAR(40),
    banned BOOLEAN,
    customer_join_time TIMESTAMP,
    gender ENUM('Male','Female')
);

INSERT INTO customer_details(customer_id,customer_name,customer_PAN,banned,customer_join_time,gender) 
VALUES (11,'salil','sa11',0,'2019-04-01','Male'),
        (12,'puran','pu12',1,'2019-04-17','Male'),
        (13,'saumya','sa12',1,'2019-01-19','Female'),
        (14,'priya','pr11',0,'2019-01-27','Female'),
        (15,'suresh','su15',0,'2019-04-27','Male'),
        (16,'amit','am11',0,'2019-04-01','Male'),
        (17,'rahul','ra45',0,'2019-01-29','Male'),
        (18,'rajesh','ra21',0,'2021-01-26','Male'),
        (19,'aswini','as65',0,'2021-01-26','Female'),
        (20,'prabha','pr95',0,'2021-04-26','Female'),
        (21,'shubham','sh01',1,'2021-04-26','Male');


SELECT c.customer_id,c.gender,m.nav_value,m.transaction_type,m.transaction_status
FROM customer_details c
INNER JOIN mutual_fund m on c.customer_id=m.customer_id
WHERE (SELECT c.gender='Male' FROM customer_details c INNER JOIN mutual_fund m on c.customer_id=m.customer_id ORDER BY m.nav_value DESC LIMIT 5) AS t1
UNION ALL
(SELECT c.gender="Female" FROM customer_details c INNER JOIN mutual_fund m on c.customer_id=m.customer_id ORDER BY m.nav_value DESC LIMIT 5) AS t2;

i am trying find out the top 5 male and top 5 female purchases(nav_value) but i am stuck here as gender is in customer_detail table and purchase(nav_value) is on mutual_fund table so using inner join and then thought union will work but i am stuck here and got no ideas left so any ideas people can this work ??????

question from:https://stackoverflow.com/questions/65935284/sql-tables-to-find-top-5-male-and-top-5-female-purchase-using-data-stored-in-two

Answer 1

You could go with something like this:

SELECT *
FROM
(
SELECT c.customer_id,c.gender,m.nav_value,m.transaction_type,m.transaction_status,
RANK() OVER (PARTITION BY c.gender ORDER BY m.nav_value DESC) AS RNum
FROM customer_details c
INNER JOIN mutual_fund m on c.customer_id=m.customer_id
) t
WHERE Rnum <= 5

You can read more about window functions here. You might want to go with one of the other window functions though, like DENSE_RANK or ROW_NUMBER depending on your needs.