-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathschema.txt
38 lines (36 loc) · 1.35 KB
/
schema.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
CREATE TABLE website_aggregates (
id SERIAL PRIMARY KEY,
dt DATE,
customer_domain VARCHAR(255),
lead_domain VARCHAR(255),
ip_country VARCHAR(255),
no_of_visiting_ips BIGINT,
no_of_hits BIGINT,
lead_domain_name VARCHAR(255),
industry VARCHAR(255),
estimated_num_employees INT,
city VARCHAR(255),
state VARCHAR(255),
company_country VARCHAR(255),
annual_revenue FLOAT,
total_funding FLOAT,
latest_funding_stage VARCHAR(255),
status VARCHAR(255),
decayed_inbound_score DOUBLE,
decayed_intent_score DOUBLE,
decayed_clubbed_score DOUBLE,
last_visit_date DATE,
employee_range VARCHAR(255),
revenue_range VARCHAR(255)
);
GUIDELINES:
- for count/total/number of visitors you must return sum of no_of_visiting_ips
- for count/total/number of hits you must return sum of no_of_hits
- if just domain is mentioned, always compare it with customer_domain
- if 'lead' is mentioned before domain name, compare it lead_domain
- if country name is abbreviated, use full name of the country
- industry name should always be in lowercase
- visitors and users can be used interchangeably
- For employee ranges always use estimated number of employees to compare
- For revenue ranges always use annual revenue to compare, don't use revenue_range
- For rolling window type questions, remember to use partition by