Task: equi-join-detect (source dataset: Auto-BI)

Your are given a database with related tables used for business intelligence analysis. Your task is to identify all key/foreign-key join relationships between the tables for this database. Recall that a join is a relationship between two tables, that allows rows from one table to be combined with rows from another, based on a set of common join columns.
 
 
No explanation needed. Please return all join relationships in the following JSON format:
{
  "joins": [
    {
      "from_table": "table-1",
      "from_column": "column-1",
      "to_table": "table-2",
      "to_column": "column-3"
    }, 
	...
  ]
}

Tables:

Table: Channels
| Sales Channel   | Channel Code   | Ccy/Channel     |
|:----------------|:---------------|:----------------|
| In-Store        | IN-ST          | USD-IN-STORE    |
| Online          | ONLI           | USD-ONLINE      |
| Distributor     | DIST           | USD-DISTRIBUTOR |
| Wholesale       | WHOL           | USD-WHOLESALE   |


Table: Customers
|   Customer Index | Customer Names   |
|-----------------:|:-----------------|
|                1 | Avon Corp        |
|                2 | WakeFern         |
|                3 | Elorac, Corp     |
|                4 | ETUDE Ltd        |
|                5 | Procter Corp     |
|                6 | PEDIFIX, Corp    |
|                7 | New Ltd          |
|                8 | Medsep Group     |
|                9 | Ei               |
|               10 | 21st Ltd         |


Table: Date
| Order Date              |   Year |   QuarterOfYear |   MonthOfYear | Short Month   | Month & Year   |   DayOfWeek | DayOfWeekName   |   ISO Weeknumber |   ISO Year | Day Type   |   WeekOffset |   MonthOffset |   QuarterOffset |   YearOffset | Calendar Quarters   |   IsWorkingDay |
|:------------------------|-------:|----------------:|--------------:|:--------------|:---------------|------------:|:----------------|-----------------:|-----------:|:-----------|-------------:|--------------:|----------------:|-------------:|:--------------------|---------------:|
| 2020-07-01 00:00:00.000 |   2020 |               3 |             7 | Jul           | Jul 2020       |           2 | Wednesday       |               27 |       2020 | Weekday    |          -27 |            -6 |              -2 |           -1 | Q3                  |              1 |
| 2020-07-02 00:00:00.000 |   2020 |               3 |             7 | Jul           | Jul 2020       |           3 | Thursday        |               27 |       2020 | Weekday    |          -27 |            -6 |              -2 |           -1 | Q3                  |              1 |
| 2020-07-03 00:00:00.000 |   2020 |               3 |             7 | Jul           | Jul 2020       |           4 | Friday          |               27 |       2020 | Weekday    |          -27 |            -6 |              -2 |           -1 | Q3                  |              1 |
| 2020-07-06 00:00:00.000 |   2020 |               3 |             7 | Jul           | Jul 2020       |           0 | Monday          |               28 |       2020 | Weekday    |          -26 |            -6 |              -2 |           -1 | Q3                  |              1 |
| 2020-07-07 00:00:00.000 |   2020 |               3 |             7 | Jul           | Jul 2020       |           1 | Tuesday         |               28 |       2020 | Weekday    |          -26 |            -6 |              -2 |           -1 | Q3                  |              1 |
| 2020-07-08 00:00:00.000 |   2020 |               3 |             7 | Jul           | Jul 2020       |           2 | Wednesday       |               28 |       2020 | Weekday    |          -26 |            -6 |              -2 |           -1 | Q3                  |              1 |
| 2020-07-09 00:00:00.000 |   2020 |               3 |             7 | Jul           | Jul 2020       |           3 | Thursday        |               28 |       2020 | Weekday    |          -26 |            -6 |              -2 |           -1 | Q3                  |              1 |
| 2020-07-10 00:00:00.000 |   2020 |               3 |             7 | Jul           | Jul 2020       |           4 | Friday          |               28 |       2020 | Weekday    |          -26 |            -6 |              -2 |           -1 | Q3                  |              1 |
| 2020-07-13 00:00:00.000 |   2020 |               3 |             7 | Jul           | Jul 2020       |           0 | Monday          |               29 |       2020 | Weekday    |          -25 |            -6 |              -2 |           -1 | Q3                  |              1 |
| 2020-07-14 00:00:00.000 |   2020 |               3 |             7 | Jul           | Jul 2020       |           1 | Tuesday         |               29 |       2020 | Weekday    |          -25 |            -6 |              -2 |           -1 | Q3                  |              1 |


Table: Products
|   Index | Product Name       | Product Subcategories   | Product Categories   |
|--------:|:-------------------|:------------------------|:---------------------|
|       1 | Cookware           | Large Diverse           | Diverse              |
|       2 | Photo Frames       | Other Big               | Others               |
|       3 | Table Lamps        | Ikea Table              | Ikea                 |
|       4 | Serveware          | Other Big               | Others               |
|       5 | Bathroom Furniture | Ikea Home               | Ikea                 |
|       6 | Computers          | Large Diverse           | Diverse              |
|       7 | Dinnerware         | Large Diverse           | Diverse              |
|       8 | Cocktail Glasses   | Medium Diverse          | Diverse              |
|       9 | Baseball           | Sports                  | Sports & Hobbies     |
|      10 | Blankets           | Ikea Bedroom            | Ikea                 |


Table: Reference
| Article                           | Provider      | Type    | Name                                                                                        | Link                                                                                                           |
|:----------------------------------|:--------------|:--------|:--------------------------------------------------------------------------------------------|:---------------------------------------------------------------------------------------------------------------|
| 3. Measures vs Calculated Columns | Blue Granite  | Blog    | UNDERSTANDING THE DIFFERENCES BETWEEN CALCULATED COLUMNS & MEASURES IN POWER BI             | https://www.bluegranite.com/blog/understanding-the-differences-between-calculated-columns-measures-in-power-bi |
| 3. Measures vs Calculated Columns | Curbal        | Youtube | DAX Fridays! #46: What is the difference between calculated column and measure in Power BI? | https://www.youtube.com/watch?v=SmXLgEHXSGc                                                                    |
| 3. Measures vs Calculated Columns | SQLBI         | Blog    | Calculated Columns and Measures in DAX                                                      | https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/                                         |
| 3. Measures vs Calculated Columns | SQLBI         | Youtube | Calculated Columns and Measures in DAX                                                      | https://www.youtube.com/watch?v=ePPi1LLX0sA&feature=emb_title                                                  |
| 3. Measures vs Calculated Columns | EnterpriseDNA | Youtube | Measures vs Calculated Columns - (1.5) Ultimate Beginners guide to DAX                      | https://www.youtube.com/watch?v=5vOEktgYWM0                                                                    |
| 3. Measures vs Calculated Columns | Radacad       | Blog    | Measure vs Calculated Column in Power BI The Mysterious Question Not                        | https://radacad.com/measure-vs-calculated-column-the-mysterious-question-not                                   |
| 3. Measures vs Calculated Columns | Radacad       | Youtube | Measure vs Calculated Column in Power BI The Mysterious Question Not                        | https://www.youtube.com/watch?v=SVEGfqCTodc                                                                    |


Table: Sales Team
|   Index | Sales Team       | Region    | Manager        |   ManagerID | FullPath   | CEO            | Regional Manager   | Sales Manager    | Employees        |   NodeDepth | IsLeaf   | Start Date   | End Date   |
|--------:|:-----------------|:----------|:---------------|------------:|:-----------|:---------------|:-------------------|:-----------------|:-----------------|------------:|:---------|:-------------|:-----------|
|       1 | Adam Hernandez   | Northeast | Adam Hernandez |           1 | 1          | Adam Hernandez | Adam Hernandez     | Adam Hernandez   | Adam Hernandez   |           1 | False    | 18/02/2018   |            |
|       2 | Keith Griffin    | Northeast | Adam Hernandez |           1 | 1|2        | Adam Hernandez | Keith Griffin      | Keith Griffin    | Keith Griffin    |           2 | False    | 19/03/2017   |            |
|       3 | Jerry Green      | West      | Jerry Green    |           2 | 1|2|3      | Adam Hernandez | Keith Griffin      | Jerry Green      | Jerry Green      |           3 | False    | 01/01/2019   |            |
|       4 | Chris Armstrong  | Northeast | Adam Hernandez |           1 | 1|4        | Adam Hernandez | Chris Armstrong    | Chris Armstrong  | Chris Armstrong  |           2 | True     | 02/04/2020   |            |
|       5 | Stephen Payne    | South     | Stephen Payne  |           3 | 1|2|3|5    | Adam Hernandez | Keith Griffin      | Jerry Green      | Stephen Payne    |           4 | True     | 03/05/2015   |            |
|       6 | Joshua Bennett   | Northeast | Adam Hernandez |           1 | 1|6        | Adam Hernandez | Joshua Bennett     | Joshua Bennett   | Joshua Bennett   |           2 | True     | 04/02/2013   |            |
|       7 | Shawn Cook       | Midwest   | Shawn Cook     |           7 | 7          | Shawn Cook     | Shawn Cook         | Shawn Cook       | Shawn Cook       |           1 | False    | 02/07/2018   |            |
|       8 | George Lewis     | West      | Jerry Green    |           2 | 1|2|8      | Adam Hernandez | Keith Griffin      | George Lewis     | George Lewis     |           3 | True     | 03/04/2019   |            |
|       9 | Joshua Ryan      | Midwest   | Shawn Cook     |           7 | 7|9        | Shawn Cook     | Joshua Ryan        | Joshua Ryan      | Joshua Ryan      |           2 | True     | 01/01/2020   |            |
|      10 | Jonathan Hawkins | West      | Jerry Green    |           2 | 1|2|10     | Adam Hernandez | Keith Griffin      | Jonathan Hawkins | Jonathan Hawkins |           3 | True     | 01/03/2020   |            |


Table: Sales
| OrderNumber   | Sales Channel   | Warehouse Code   | Procured Date           | OrderDate               | Ship Date               | Delivery Date           |   Sales Team Index |   Customer Index |   Store Index |   Product Index |   Order Quantity |   Discount Applied |   Unit Price |   Unit Cost |
|:--------------|:----------------|:-----------------|:------------------------|:------------------------|:------------------------|:------------------------|-------------------:|-----------------:|--------------:|----------------:|-----------------:|-------------------:|-------------:|------------:|
| SO - 000140   | In-Store        | WARE-NMK1003     | 2017-12-31 00:00:00.000 | 2018-06-03 00:00:00.000 | 2018-06-18 00:00:00.000 | 2018-06-28 00:00:00.000 |                  9 |               26 |           196 |              25 |                4 |               0.05 |       1132.3 |     486.889 |
| SO - 000164   | In-Store        | WARE-NMK1003     | 2018-04-10 00:00:00.000 | 2018-06-07 00:00:00.000 | 2018-07-01 00:00:00.000 | 2018-07-02 00:00:00.000 |                  5 |               40 |           120 |              46 |                7 |               0.05 |       2257.9 |    1761.16  |
| SO - 000174   | In-Store        | WARE-NMK1003     | 2018-04-10 00:00:00.000 | 2018-06-08 00:00:00.000 | 2018-06-30 00:00:00.000 | 2018-07-06 00:00:00.000 |                  3 |                7 |           140 |              22 |                3 |               0.05 |       2659.9 |    2154.52  |
| SO - 000215   | In-Store        | WARE-NMK1003     | 2017-12-31 00:00:00.000 | 2018-06-14 00:00:00.000 | 2018-06-19 00:00:00.000 | 2018-06-20 00:00:00.000 |                  2 |               13 |           169 |              40 |                2 |               0.05 |       2546   |    1909.5   |
| SO - 000255   | In-Store        | WARE-NMK1003     | 2017-12-31 00:00:00.000 | 2018-06-18 00:00:00.000 | 2018-06-22 00:00:00.000 | 2018-06-26 00:00:00.000 |                  6 |               22 |           101 |               5 |                4 |               0.05 |        221.1 |     126.027 |
| SO - 000260   | In-Store        | WARE-NMK1003     | 2018-04-10 00:00:00.000 | 2018-06-18 00:00:00.000 | 2018-07-02 00:00:00.000 | 2018-07-07 00:00:00.000 |                 10 |               26 |           119 |              35 |                1 |               0.05 |       1005   |     412.05  |
| SO - 000266   | In-Store        | WARE-NMK1003     | 2017-12-31 00:00:00.000 | 2018-06-19 00:00:00.000 | 2018-06-28 00:00:00.000 | 2018-07-04 00:00:00.000 |                  1 |               19 |           126 |              19 |                6 |               0.05 |       6324.8 |    4300.86  |
| SO - 000310   | In-Store        | WARE-NMK1003     | 2018-04-10 00:00:00.000 | 2018-06-26 00:00:00.000 | 2018-07-16 00:00:00.000 | 2018-07-18 00:00:00.000 |                  1 |               14 |           187 |              22 |                8 |               0.05 |       1105.5 |     762.795 |
| SO - 000318   | In-Store        | WARE-NMK1003     | 2018-04-10 00:00:00.000 | 2018-06-27 00:00:00.000 | 2018-07-04 00:00:00.000 | 2018-07-06 00:00:00.000 |                  3 |               22 |           108 |              43 |                2 |               0.05 |       3852.5 |    2003.3   |
| SO - 000323   | In-Store        | WARE-NMK1003     | 2018-04-10 00:00:00.000 | 2018-06-27 00:00:00.000 | 2018-07-18 00:00:00.000 | 2018-07-22 00:00:00.000 |                  9 |                6 |           134 |              47 |                7 |               0.05 |       2479   |    2057.57  |


Table: Store Regions
|   ID | City Name   | County              | State Code   | State      | Type   |   Latitude |   Longitude |   Area Code |   Population |   Households |   Median Income |   Land Area |   Water Area | Times Zone          | State Region   |
|-----:|:------------|:--------------------|:-------------|:-----------|:-------|-----------:|------------:|------------:|-------------:|-------------:|----------------:|------------:|-------------:|:--------------------|:---------------|
|   16 | Anaheim     | Orange County       | CA           | California | City   |    33.8353 |    -117.915 |         657 |       350742 |        99670 |           60752 |   129438076 |      2457142 | America/Los Angeles | West           |
|   17 | Antioch     | Contra Costa County | CA           | California | City   |    38.0049 |    -121.806 |         925 |       110542 |        33718 |           64329 |    76173197 |      1842711 | America/Los Angeles | West           |
|   18 | Bakersfield | Kern County         | CA           | California | City   |    35.3733 |    -119.019 |         661 |       373640 |       112439 |           57095 |   385366784 |      3809676 | America/Los Angeles | West           |
|   19 | Berkeley    | Alameda County      | CA           | California | City   |    37.8716 |    -122.273 |         510 |       120972 |        45917 |           66237 |    27106077 |     18715614 | America/Los Angeles | West           |
|   20 | Burbank     | Los Angeles County  | CA           | California | City   |    34.1808 |    -118.309 |         818 |       105319 |        41361 |           66076 |    44947219 |        94286 | America/Los Angeles | West           |
|   21 | Carlsbad    | San Diego County    | CA           | California | City   |    33.1581 |    -117.351 |         760 |       113453 |        42791 |           90597 |    97713477 |      3595655 | America/Los Angeles | West           |
|   22 | Chula Vista | San Diego County    | CA           | California | City   |    32.64   |    -117.084 |         619 |       265757 |        78066 |           65185 |   128544440 |      6380135 | America/Los Angeles | West           |
|   23 | Clovis      | Fresno County       | CA           | California | City   |    36.8252 |    -119.703 |         559 |       104180 |        34512 |           62666 |    62693928 |            0 | America/Los Angeles | West           |
|   24 | Concord     | Contra Costa County | CA           | California | City   |    37.978  |    -122.031 |         925 |       128667 |        45409 |           68318 |    79108534 |            0 | America/Los Angeles | West           |
|   25 | Corona      | Riverside County    | CA           | California | City   |    33.8753 |    -117.566 |         951 |       164226 |        48156 |           74149 |   102233537 |       211897 | America/Los Angeles | West           |


Table: Warehouses
| Warehouse Code   | Warehouse Abv   | Warehouses   |
|:-----------------|:----------------|:-------------|
| WARE-UHY1004     | UHY1004         | uhy          |
| WARE-NMK1003     | NMK1003         | nmk          |
| WARE-PUJ1005     | PUJ1005         | puj          |
| WARE-XYS1001     | XYS1001         | xys          |
| WARE-MKL1006     | MKL1006         | mkl          |
| WARE-NBV1002     | NBV1002         | nbv          |

 


tables: name: Channels
data: Channels.csv
name: Customers
data: Customers.csv
name: Date
data: Date.csv
name: Products
data: Products.csv
name: Reference
data: Reference.csv
name: Sales Team
data: Sales Team.csv
name: Sales
data: Sales.csv
name: Store Regions
data: Store Regions.csv
name: Warehouses
data: Warehouses.csv
label: Sales
Sales Channel
Channels
Sales Channel
Sales
Customer Index
Customers
Customer Index
Sales
Warehouse Code
Warehouses
Warehouse Code
Sales
Store Index
Store Regions
ID
Sales
Sales Team Index
Sales Team
Index
Sales
OrderDate
Date
Order Date
Sales
Product Index
Products
Index
output: {
    "joins": [
        {
            "from_table": "Sales",
            "from_column": "Sales Channel",
            "to_table": "Channels",
            "to_column": "Sales Channel"
        },
        {
            "from_table": "Sales",
            "from_column": "Customer Index",
            "to_table": "Customers",
            "to_column": "Customer Index"
        },
        {
            "from_table": "Sales",
            "from_column": "Warehouse Code",
            "to_table": "Warehouses",
            "to_column": "Warehouse Code"
        },
        {
            "from_table": "Sales",
            "from_column": "Store Index",
            "to_table": "Store Regions",
            "to_column": "ID"
        },
        {
            "from_table": "Sales",
            "from_column": "Sales Team Index",
            "to_table": "Sales Team",
            "to_column": "Index"
        },
        {
            "from_table": "Sales",
            "from_column": "OrderDate",
            "to_table": "Date",
            "to_column": "Order Date"
        },
        {
            "from_table": "Sales",
            "from_column": "Product Index",
            "to_table": "Products",
            "to_column": "Index"
        }
    ]
}