As part of HackerAd’s advertising system analytics, a team needs a list of campaigns that have a sequence of events with more sell transactions than buy transactions. Transactions are considered in the order they appear in the table. A sequence is a number of consecutive transactions with the same campaign_id.
The result should be in the following format: campaign ,netsells_count, netsells_total.

1.campaign is the campaign name. A campaign name may have more than one campaign id.
2.netsells_count is the total number of reported event sequences for a campaign name.
3.netsells_total is a total of all the sell transactions in event sequences.
4.Results should be sorted descending by netsells_total.
Schema
There are 2 tables:
FIRST TABLE – campaigns
Below is Description of table campaigns
name type description
id SMALLINT Campaign ID
name VARCHAR(64) Campaign name

SECOND TABLE- events
Below is Description of table events

name type description
campaign_id SMALLINT Campaign ID
type VARCHAR(5) Event type
amount DECIMAL(5,2) Transaction amount

Transactions are considered in the order they appear. A sequence is a number of
consecutive transactions with the same campaign_id. A sequence should be
reported if the number of sell transactions exceeds the number of buy transactions.
The first sequence in the sample tables is
1 sell 28.47
2 buy 51.97
3 sell 48.89

There are two sell transactions and only one buy transaction, so this sequence is
reported. The sell transactions add to 77.36 and the name is ‘Engineering’.
The next sequence has only sell transactions, so it is reported. The sell transactions
add to 24.02. The name is ‘Engineering’.
3 sell 17.37
3 sell 6.65

The third sequence qualifies with two sells and one buy. The sell transactions sum to
71.63, and the name is ‘Engineering”.
1 sell 7.13
1 sell 64.50
1 buy 31.01

The final sequence does not qualify since it does not have more sell than buy
transactions.
2 sell 10.51
2 buy 9.89
The qualifying sequences are associated with 2 different campaigns: 1 and 3. Both
campaigns are named Engineering. The total of sell amounts is 77.36 +24.02+71.63
= 173.01.
The expected output is:
campaign netsells _count netsells _total
Engineering 2 173.01

Query for sample data
sample data create database advertise;
use advertise;
create table campaigns(id SMALLINT,name VARCHAR(64) );
create table events(campaign_id SMALLINT ,type VARCHAR(5) ,amount DECIMAL(5,2) );
insert into campaigns values (1 ,’engineering’),(2,’legal’),(3,’engineering’);
insert into events values (2,’buy’,54.70),(1,’sell’,76.02),(2,’sell’,29.47), (1,’buy’,46.32),(3,’sell’,2.96),(2,’sell’,28.96),(3,’buy’,66.31),(2,’buy’,89.60), (1,’sell’,28.47),(1,’buy’,51.97),(1,’sell’,48.89),(3,’sell’,17.37),(3,’sell’,6.65), (2,’buy’,9.41),(1,’sell’,7.13),(1,’sell’,64.50),(1,’buy’,31.01), (2,’sell’,10.51),(2,’buy’,9.89),(3,’sell’,74.79);

Sample output table

Marketing 5 662.42
Services 6 639.14
Engineering 2 155.16
Business Development 1 146.30
Support 2 116.63
Legal 1 72.22

Research and Development 1 66.76

I tried this query,
SELECT
c.name AS campaign,
COUNT(DISTINCT e1.campaign_id) AS netsells_count,
SUM(CASE WHEN e1.type = ‘sell’ THEN e1.amount ELSE 0 END) AS netsells_total
FROM campaigns AS c
INNER JOIN events AS e1 ON c.id = e1.campaign_id
WHERE (
SELECT COUNT() FROM events AS e2
WHERE e2.campaign_id = c.id AND e2.type = ‘sell’
) > (
SELECT COUNT(
) FROM events AS e3
WHERE e3.campaign_id = c.id AND e3.type = ‘buy’
)
GROUP BY campaign
ORDER BY netsells_total DESC;

but the output im getting is wrong.The o/p im getting
engineering 2 326.78

New contributor

captainshally is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

Khám phá các thẻ bài đăng