SQLTeam.com | Weblogs | Forums

Can someone help me? I dont know what are the stages i need to take

Tables In CSV

  • “Installs” has data about the amount of installs and GrossRev for each country.

GrossRev – The Revenue before Rev Share

  • “Rates” has data about the payout we pay for an install in each country.

Table in this document

  • “Rev Share” has data about the monthly revenues tiered model.

The ”Rates” table contains country, rate and the startdate . For example, the rate for BR install from 1.1.2020 is 0.01$ , But from 10.1.2020 the install rate changed to 0.02$

Write SQL query that will output the total traffic Net Revenues by country .

Expected results : Month, Country , Net Revenues

Write SQL query that will output the total traffic buying expenses by country.

Expected results : Month, Country, traffic buying expenses

“Installs” table example:

InstallDate Country Installs GrossRev
1.1.2020 BR 1363 15601
1.1.2020 CA 1241 12500
1.1.2020 US 1106 200000
1.2.2020 GB 1559 15000
1.2.2020 DE 1717 17650
1.3.2020 DK 1314 13140
1.3.2020 FI 1105 11050

“Rates” table example:

StartDate Country Rate
1.1.2020 BR 0.01 $
1.1.2020 CA 0.7 $
1.1.2020 US 0.35 $
1.1.2020 DE 0.3 $
10.1.2020 BR 0.02$

“Rev Share” Table:

Amount of Monthly Revenue Revenue Share
Less than 100,000 $ 70%
$100,000 – $250,000 73%
$250,000 - $1,000,000 76%
Over $1,000,000 80%