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: calendar
| Date       |   Year | Month Name   |   Days in Month |   Month |   Quarter |   Week of Year |   Week of Month | Day Name   |   Day |
|:-----------|-------:|:-------------|----------------:|--------:|----------:|---------------:|----------------:|:-----------|------:|
| 01/01/2020 |   2020 | January      |              31 |       1 |         1 |              1 |               1 | Wednesday  |     1 |
| 02/01/2020 |   2020 | January      |              31 |       1 |         1 |              1 |               1 | Thursday   |     2 |
| 03/01/2020 |   2020 | January      |              31 |       1 |         1 |              1 |               1 | Friday     |     3 |
| 04/01/2020 |   2020 | January      |              31 |       1 |         1 |              1 |               1 | Saturday   |     4 |
| 05/01/2020 |   2020 | January      |              31 |       1 |         1 |              1 |               1 | Sunday     |     5 |
| 06/01/2020 |   2020 | January      |              31 |       1 |         1 |              2 |               2 | Monday     |     6 |
| 07/01/2020 |   2020 | January      |              31 |       1 |         1 |              2 |               2 | Tuesday    |     7 |
| 08/01/2020 |   2020 | January      |              31 |       1 |         1 |              2 |               2 | Wednesday  |     8 |
| 09/01/2020 |   2020 | January      |              31 |       1 |         1 |              2 |               2 | Thursday   |     9 |
| 10/01/2020 |   2020 | January      |              31 |       1 |         1 |              2 |               2 | Friday     |    10 |


Table: Customer resolution
|   Claims |   Endorsements |   Onboardings |   Support |   Grand Total | customer resolution    |
|---------:|---------------:|--------------:|----------:|--------------:|:-----------------------|
|        4 |              4 |             0 |         2 |            10 | 1 times                |
|        0 |              4 |             0 |         1 |             5 | 2 times                |
|        3 |              1 |             0 |         3 |             7 | 3 times                |
|        0 |              5 |             0 |         0 |             5 | 4 times                |
|        6 |              4 |             0 |         9 |            19 | 5 times                |
|        0 |              0 |             0 |         1 |             1 | 8 times                |


Table: Email_ticketing
| Email_ticket_status           |   Claims |   Endorsements |   Onboardings |   Support |   Grand Total |
|:------------------------------|---------:|---------------:|--------------:|----------:|--------------:|
| Closed                        |       26 |            139 |             6 |       248 |           419 |
| Open                          |       30 |            268 |            52 |       103 |           453 |
| Pending                       |       26 |              6 |            10 |         0 |            42 |
| Previous batch in process     |        0 |             34 |             0 |         0 |            34 |
| Resolved                      |       11 |              8 |             0 |         0 |            19 |
| Waiting for data corrections  |        0 |              3 |             0 |         0 |             3 |
| Waiting on CD account process |        0 |              2 |             0 |         0 |             2 |
| Waiting on CD balance         |        0 |              4 |             0 |         0 |             4 |
| Waiting on Endorsement        |        0 |             30 |             0 |         0 |            30 |
| Waiting on insurer            |        6 |              3 |             0 |         0 |             9 |


Table: First_response
| First_response   |   Claims |   Endorsements |   Onboardings |   Support |   Grand Total |
|:-----------------|---------:|---------------:|--------------:|----------:|--------------:|
| SLA Violated     |       56 |             32 |             5 |        66 |           159 |
| Within SLA       |        5 |             94 |             3 |        31 |           133 |
| No response      |       38 |            379 |            60 |       254 |           731 |


Table: Group
| Group_name   |   Total customer resolution |   Number of customer resolution |   number_customer_interaction |   number agent interactions |   Total Customer interactions |   Total Agent interactions |
|:-------------|----------------------------:|--------------------------------:|------------------------------:|----------------------------:|------------------------------:|---------------------------:|
| Claims       |                          43 |                              99 |                            99 |                          99 |                           173 |                         99 |
| Endorsements |                          55 |                             505 |                           505 |                         505 |                           561 |                        167 |
| Onboardings  |                           0 |                              68 |                            68 |                          68 |                           106 |                         72 |
| Support      |                          66 |                             351 |                           351 |                         351 |                           430 |                        116 |


Table: resolution_status
| reolution_status   |   Claims |   Endorsements |   Onboardings |   Support |   Grand Total |
|:-------------------|---------:|---------------:|--------------:|----------:|--------------:|
| SLA Violated       |       35 |              0 |             5 |        19 |            59 |
| Within SLA         |        2 |            147 |             1 |       229 |           379 |
| No response        |       62 |            358 |            62 |       103 |           585 |


Table: SLA
|   No response |   SLA Violated |   Within SLA |   Grand Total | SLA days_Brief   |
|--------------:|---------------:|-------------:|--------------:|:-----------------|
|            99 |             35 |            3 |           137 | 0 days           |
|            20 |              3 |            4 |            27 | 1 days           |
|            78 |             10 |          121 |           209 | 2 days           |
|            11 |              1 |           25 |            37 | 3 days           |
|            17 |             10 |           79 |           106 | 4 days           |
|             2 |              0 |            0 |             2 | 5 days           |
|           159 |              0 |           83 |           242 | 15 days          |
|            60 |              0 |           17 |            77 | 16 days          |
|            83 |              0 |           42 |           125 | 17 days          |
|            56 |              0 |            4 |            60 | 18 days          |


Table: Source_Data
| Email Ticket Status   | Group        | Created time        | Due by Time         | Closed time         | Last update time    | Initial response time   | First response time (in hrs)   |   Agent interactions |   Customer interactions | Resolution status   | First response status   |   CSAT (Happiness) |   Resolution time (in hrs) | Created Date   |   Created hour | Due Date   |   due hours |   sla days for completion(days) |   Resolution time (in hrs)_1 |   Resolution Min |   Resolution in Sec |   Resolution_days | sla_days_final   |
|:----------------------|:-------------|:--------------------|:--------------------|:--------------------|:--------------------|:------------------------|:-------------------------------|---------------------:|------------------------:|:--------------------|:------------------------|-------------------:|---------------------------:|:---------------|---------------:|:-----------|------------:|--------------------------------:|-----------------------------:|-----------------:|--------------------:|------------------:|:-----------------|
| Open                  | Endorsements | 2021-10-07 07:00:11 | 2021-10-22 19:00:00 | 0000-00-00 00:00:00 | 2021-10-08 19:03:23 | 0000-00-00 00:00:00     | 00:00:00                       |                    0 |                       1 | No response         | No response             |                  0 |                          0 | 07/10/2021     |       0.291794 | 22/10/2021 |    0.791667 |                              15 |                            0 |                0 |                   0 |                 0 | 15 days          |
| Open                  | Endorsements | 2021-10-07 07:00:11 | 2021-10-22 19:00:00 | 0000-00-00 00:00:00 | 2021-10-08 19:03:23 | 0000-00-00 00:00:00     | 00:00:00                       |                    0 |                       1 | No response         | No response             |                  0 |                          0 | 07/10/2021     |       0.291794 | 22/10/2021 |    0.791667 |                              15 |                            0 |                0 |                   0 |                 0 | 15 days          |
| Open                  | Endorsements | 2021-10-07 07:00:11 | 2021-10-22 19:00:00 | 0000-00-00 00:00:00 | 2021-10-08 19:03:24 | 0000-00-00 00:00:00     | 00:00:00                       |                    0 |                       1 | No response         | No response             |                  0 |                          0 | 07/10/2021     |       0.291794 | 22/10/2021 |    0.791667 |                              15 |                            0 |                0 |                   0 |                 0 | 15 days          |
| Open                  | Endorsements | 2021-10-07 07:00:11 | 2021-10-22 19:00:00 | 0000-00-00 00:00:00 | 2021-10-08 19:03:24 | 0000-00-00 00:00:00     | 00:00:00                       |                    0 |                       1 | No response         | No response             |                  0 |                          0 | 07/10/2021     |       0.291794 | 22/10/2021 |    0.791667 |                              15 |                            0 |                0 |                   0 |                 0 | 15 days          |
| Open                  | Endorsements | 2021-10-07 07:00:11 | 2021-10-22 19:00:00 | 0000-00-00 00:00:00 | 2021-10-08 19:03:24 | 0000-00-00 00:00:00     | 00:00:00                       |                    0 |                       1 | No response         | No response             |                  0 |                          0 | 07/10/2021     |       0.291794 | 22/10/2021 |    0.791667 |                              15 |                            0 |                0 |                   0 |                 0 | 15 days          |
| Open                  | Endorsements | 2021-10-07 07:00:11 | 2021-10-22 19:00:00 | 0000-00-00 00:00:00 | 2021-10-08 19:03:25 | 0000-00-00 00:00:00     | 00:00:00                       |                    0 |                       1 | No response         | No response             |                  0 |                          0 | 07/10/2021     |       0.291794 | 22/10/2021 |    0.791667 |                              15 |                            0 |                0 |                   0 |                 0 | 15 days          |
| Open                  | Endorsements | 2021-10-09 07:00:11 | 2021-10-26 19:00:00 | 0000-00-00 00:00:00 | 2021-10-13 17:56:30 | 0000-00-00 00:00:00     | 00:00:00                       |                    0 |                       1 | No response         | No response             |                  0 |                          0 | 09/10/2021     |       0.291794 | 26/10/2021 |    0.791667 |                              17 |                            0 |                0 |                   0 |                 0 | 17 days          |
| Open                  | Endorsements | 2021-10-09 07:00:11 | 2021-10-26 19:00:00 | 0000-00-00 00:00:00 | 2021-10-13 17:56:31 | 0000-00-00 00:00:00     | 00:00:00                       |                    0 |                       1 | No response         | No response             |                  0 |                          0 | 09/10/2021     |       0.291794 | 26/10/2021 |    0.791667 |                              17 |                            0 |                0 |                   0 |                 0 | 17 days          |
| Open                  | Endorsements | 2021-10-09 07:00:11 | 2021-10-26 19:00:00 | 0000-00-00 00:00:00 | 2021-10-13 18:00:45 | 0000-00-00 00:00:00     | 00:00:00                       |                    0 |                       1 | No response         | No response             |                  0 |                          0 | 09/10/2021     |       0.291794 | 26/10/2021 |    0.791667 |                              17 |                            0 |                0 |                   0 |                 0 | 17 days          |
| Open                  | Endorsements | 2021-10-09 07:00:11 | 2021-10-26 19:00:00 | 0000-00-00 00:00:00 | 2021-10-13 17:57:02 | 0000-00-00 00:00:00     | 00:00:00                       |                    0 |                       1 | No response         | No response             |                  0 |                          0 | 09/10/2021     |       0.291794 | 26/10/2021 |    0.791667 |                              17 |                            0 |                0 |                   0 |                 0 | 17 days          |


Table: Ticket's_created-Groups
| Created_Date            |   Claims |   Endorsements |   Onboardings |   Support |   Grand Total |
|:------------------------|---------:|---------------:|--------------:|----------:|--------------:|
| 2021-10-06 00:00:00.000 |       13 |             71 |            11 |        88 |           183 |
| 2021-10-07 00:00:00.000 |       12 |             83 |            20 |        58 |           173 |
| 2021-10-08 00:00:00.000 |       18 |             94 |            10 |        51 |           173 |
| 2021-10-09 00:00:00.000 |        3 |             63 |             1 |        29 |            96 |
| 2021-10-10 00:00:00.000 |        8 |             21 |             0 |         4 |            33 |
| 2021-10-11 00:00:00.000 |       20 |             30 |            12 |        43 |           105 |
| 2021-10-12 00:00:00.000 |       14 |             75 |            10 |        43 |           142 |
| 2021-10-13 00:00:00.000 |       11 |             68 |             4 |        35 |           118 |

 


tables: name: calendar
data: calendar.csv
name: Customer resolution
data: Customer resolution.csv
name: Email_ticketing
data: Email_ticketing.csv
name: First_response
data: First_response.csv
name: Group
data: Group.csv
name: resolution_status
data: resolution_status.csv
name: SLA
data: SLA.csv
name: Source_Data
data: Source_Data.csv
name: Ticket's_created-Groups
data: Ticket's_created-Groups.csv
label: Source_Data
Group
Group
Group_name
Source_Data
Created time
calendar
Date
Source_Data
Resolution status
resolution_status
reolution_status
Source_Data
First response status
First_response
First_response
Source_Data
Email Ticket Status
Email_ticketing
Email_ticket_status
Source_Data
Created Date
Ticket's_created-Groups
Created_Date
Source_Data
CSAT (Happiness)
Customer resolution
customer resolution 
Source_Data
sla days for completion(days)
SLA
SLA days_Brief
output: {
    "joins": [
        {
            "from_table": "Source_Data",
            "from_column": "Group",
            "to_table": "Group",
            "to_column": "Group_name"
        },
        {
            "from_table": "Source_Data",
            "from_column": "Created time",
            "to_table": "calendar",
            "to_column": "Date"
        },
        {
            "from_table": "Source_Data",
            "from_column": "Resolution status",
            "to_table": "resolution_status",
            "to_column": "reolution_status"
        },
        {
            "from_table": "Source_Data",
            "from_column": "First response status",
            "to_table": "First_response",
            "to_column": "First_response"
        },
        {
            "from_table": "Source_Data",
            "from_column": "Email Ticket Status",
            "to_table": "Email_ticketing",
            "to_column": "Email_ticket_status"
        },
        {
            "from_table": "Source_Data",
            "from_column": "Created Date",
            "to_table": "Ticket's_created-Groups",
            "to_column": "Created_Date"
        },
        {
            "from_table": "Source_Data",
            "from_column": "CSAT (Happiness)",
            "to_table": "Customer resolution",
            "to_column": "customer resolution "
        },
        {
            "from_table": "Source_Data",
            "from_column": "sla days for completion(days)",
            "to_table": "SLA",
            "to_column": "SLA days_Brief"
        }
    ]
}