[Solved] Find the top 30% of the industry’s revenue in the past 12 months through SQL [closed]

Question

This Query will definitely help you

SELECT A.*, P.30_PERCENT_REVENUE FROM STACK_USER.revenue AS A
JOIN
(SELECT TRIM(B.industry_category) AS industry_category, (((SELECT SUM(REVENUE) FROM STACK_USER.revenue WHERE industry_category=B.industry_category)/100) *30) 
AS 30_PERCENT_REVENUE FROM STACK_USER.revenue AS B GROUP BY B.industry_category AND MONTH(DATE)>=MONTH(SYSDATE()) AND YEAR(DATE)>=YEAR(SYSDATE())-1) AS P
ON TRIM(A.industry_category)=P.industry_category AND A.revenue>=P.30_PERCENT_REVENUE;

I was very confused by your question because the question is not that much clear

so here what I understood is, you have to calculate all category revenue (30%) past 12 month

and the (industry_surname) whose revenue is greater than the past 12 months 30% category revenue so this will give only that result but

here I used the date as a full date below is the script of creating the table

CREATE TABLE STACK_USER.REVENUE
(
        INDUSTRY_SURNAME VARCHAR(100), 
        REVENUE FLOAT, DATE DATE, 
        INDUSTRY_CATEGORY VARCHAR(100)
);

And inserted this record and performed operation below is the insert script

INSERT INTO REVENUE (INDUSTRY_SURNAME, REVENUE, DATE, INDUSTRY_CATEGORY) VALUES 
('A' ,2000,'2020/01/01' ,'Agriculture'),
('B' ,4000,'2020/09/01','Industrial'),
('C',9900,'2020/05/01','Agriculture'),
('D',6000,'2020/09/01','Industrial'),
('A' ,2000,'2020/02/01','Agriculture'),
('B' ,3500,'2020/08/01','Industrial'),
('A' ,1000,'2020/05/01','Agriculture'),
('A' ,7000,'2020/08/01' ,'Agriculture'),
('A' ,5000,'2020/12/01','Agriculture');

I Hope you will like my hard work and literally happy to answer your question

if i fulfilled you requirement then please follow me and upvote my answer i would be very happy

Accepted Answer