Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

A sequence is a number of consecutive transactions with the same campaign _ id . The result should be in the following format : campaign,

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.
Results should be sorted descending by netsells_total.
sample input data
Campaigns
id name
1 Engineering
2 Legal
3 Engineering
Events
Camapign_id Type amount
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
2 sell 3.45
2 sell 54.70
2 sell 76.02
output:
Campaign netsells_count Netsells_total
Engineering 2173.01
legal 1134.17
Explanation:
1.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 buys transactions.
the first sequence in the sample tables is
Campaign_id type amount
1 sell 28.47
1 buy 51.97
1 sell 48.89
There are two sell transactions and only one buy transactions, so this sequence is reported. The sell transactions add to 77.36 and the name is 'Engineering'.
2.The next sequence has only sell transactions, so it is reported. The sell transactions add to 24.02. The name is 'Engineering'.
campaign_id type amount
3 sell 117.37
3 sell 6.65
3.The third sequence qualifies with two sells and one buy. The sell transactions sum to 71.63, and the name is 'Engineering'.
Campaign_id type amount
1 sell 7.13
1 sell 64.50
1 buy 31.01
4.The final sequence does not qualify since it does not have more sell than buy transactions.
campaign_id type amount
2 sell 10.51
2 buy 9.8
5.The qualifying sequences are associated with 2 different campaigns id 1 and 3
Both campaigns are named Engineering. The total of sell amounts is 77.36+24.02+71.63=173.01
I need my output to be this:
Campaign netsells_count Netsells_total
Engineering 2173.01

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Intelligent Information And Database Systems Third International Conference Achids 2011 Daegu Korea April 2011 Proceedings Part 2 Lnai 6592

Authors: Ngoc Thanh Nguyen ,Chong-Gun Kim ,Adam Janiak

2011th Edition

3642200419, 978-3642200410

More Books

Students also viewed these Databases questions

Question

13.5 Multicollinearity

Answered: 1 week ago

Question

f. Did they change their names? For what reasons?

Answered: 1 week ago