cancel
Showing results for 
Search instead for 
Did you mean: 

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

Rasha
Employee
Employee

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:Screenshot 2023-03-21 at 3.18.35 PM.png

 

 
1 REPLY 1

StevenPeng
Partner
Partner

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
StevenPeng_0-1679510456216.png