Challenge #7 How to Filter by a subquery in Incorta (usage of the In-Query) filter

[Rasha]

In this Challenge, We need to get the cost and revenue for Customer Sales located in a specific country i.e in "Germany",  It should be equivalent to the below SQL statement:

SELECT SALES.CUSTOMERS.CUSTOMER_NAME,
SALES.COST,
SALES.REVENUE
FROM SALES
JOIN CUSTOMERS ON (SALES.CUSTOMER_ID = CUSTOMERS.CUST_ID)
WHERE CUSTOMERS.COUNTRY_ID IN (SELECT COUNTRY_ID FROM COUNTRIES WHERE 
COUNTRY_NAME = 'Germany')

Using the attached Sales, Customers, and Countries data files

The output should be like this:

[StevenPeng]

Hi Rasha, here's my solution:

1. Upload csv and load tables

2. Set up join between sales and customers

3. Create insights with "In Query" individual filter:  

queryDistinct(

    Challenge.COUNTRIES.COUNTRY_ID, 

    Challenge.COUNTRIES.COUNTRY_NAME = "Germany"

)

With this approach, we don't have to set up join between customers and countries table