Calculate Total Revenue in USD Using SQL Query

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Meta.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Meta during technical interviews.

Question

ads_revenue

+---------+------------+---------+----------+
| ad_id   | country    | revenue | currency |
+---------+------------+---------+----------+
| 101     | US         | 120.00  | USD      |
| 102     | DE         | 90.00   | EUR      |
| 103     | JP         | 13000   | JPY      |
| 104     | IN         | 7000    | INR      |
| 105     | FR         | 85.00   | EUR      |
+---------+------------+---------+----------+

exchange_rate

+----------+----------+
| currency | usd_rate |
+----------+----------+
| USD      | 1.00     |
| EUR      | 1.08     |
| JPY      | 0.0065   |
| INR      | 0.012    |
+----------+----------+

##### Scenario

An ad-tech team stores revenue generated from ad impressions in various currencies and needs total revenue in USD for daily reporting.

##### Question

Write a SQL query that returns the total advertising revenue converted to USD, using the revenue and currency columns in ads_revenue and today’s rates in exchange_rate.

##### Hints

Join ads_revenue with exchange_rate, multiply revenue by usd_rate, then SUM.

PracHub · Accepted Answer

This question evaluates proficiency in SQL data manipulation, focusing on joins, aggregations, and handling currency conversion across relational tables.

Quick Overview

Quick Overview