Task: Design a data warehouse solution to support rapid lookup of some key metrics. (Many more details below)

What you will submit for this task:

A description and/or diagram of any tables you would make, showing what columns or fields would be included, how the tables relate to each other (or don’t), and how they relate to the source data. (Communicating table names and what they hold is sufficient, you don’t need to exhaustively list every field.) 

Note: we are not expecting a polished and professional diagram. A working sketch that communicates your ideas is sufficient. Feel free to draw on paper and snap a picture if that’s fastest for you!).

Write the SQL for creating at least one of the tables in your design from the source tables.

Write an SQL query that could be run against your design to answer one of the business questions provided.

Note: SQL is meant to be a pseudocode “pencil and paper” exercise. We are not evaluating your knowledge of correct syntax or nuanced functionality of any particular flavor of SQL, but rather your ability to communicate your design with precision as demonstrated by the code you would write. We are looking for semantic precision not syntactic. 

A brief written justification for your design choices, including assumptions you made, and any other considerations you think are important. (Some of this information may naturally be included in your response to #1, if so that’s fine, don’t repeat it here).

How to submit:

Put your responses to all 4 tasks into a single document or slide deck (google doc or google slides preferred, but docx or pptx is fine too).
Remove your name from anywhere in task materials. (We review these tasks de-identified to avoid bias in judging the work product)
Task Details

Background: Data analysts on the team, and novice SQLers in the org, need quick ways to look up the business metrics and answer the business questions described below. As the analytics engineer, your task is to propose a design for analytics tables to support these metrics building from a set of source tables that are loaded from our production database. (more details and information about the production tables are provided below.)

Here are the business metrics we need to support.

Metric Name
Business definition
Weekly Active Users
signed in at least once within the previous 7 days
Monthly Active Users
signed in at least once within the previous 30 days
Yearly Active Users
signed in at least once within the previous 365 days
Historic weekly actives
For every week (by week number 1, 2, 3…52) of every year since 2013, the number of distinct users who signed in at least once
Historic monthly actives
For every month (by month number 1, 2, 3…12) of every year since 2013 the number of distinct users who signed in at least once
Historic yearly actives
For every year since 2013 the number of distinct users who signed in at least once

Segments that need to be supported on these metrics:
Student v. Teacher accounts (see: users.user\_type)
U.S. v. International users (see: user\_geos.country)
Trained\* v Untrained teachers (see: teachers\_trained)

Some typical business questions we might get:

What do our historic number of monthly active users look like for both teachers and students, month over month, for the past 5 years?
Identify users who were active this month but not this week (i.e. active 7-30 days ago)
In the last 3 years, how many users were only active for a single month
What’s the average number of active months for US v international teachers.
Are trained teachers typically active for longer than untrained teachers?
How many users who were active at any point last year are active this month?

Data Tables: You should complete the task considering the four tables (that have been loaded into a Redshift database) given in the diagram and descriptions below. This diagram shows some of the key relationships between the tables. The key linkages are made via the user id, but be aware of one-to-many and many-to-many relationships.

Table: users
A table of information about each user. One row per user.id
users
Notes
id
integer
Matches user\_id field in other tables
username
varchar(255)
name
varchar(255)
birthday
timestamp
user\_type
varchar(48)
One of: ‘student’ or ‘teacher’
created\_at
timestamp
When this user record was created
updated\_at
timestamp
When this user record was most recently updated

Table: sign\_ins
A record of every successful user sign-in to our website. One row per user-sign-in.
sign\_ins
Notes
id
integer
user\_id
integer
sign\_in\_at
timestamp
sign\_in\_count
integer
Cumulative total # sign-ins for this user (1-n)

Table: user\_geos
A record of the geolocated IP address associated with a user. One record per user\_id.
user\_geos
Notes
id
integer
user\_id
integer
created\_at
timestamp
When this geo record for this user was first created
updated\_at
timestamp
When this geo record was most recently updated
ip\_address
varchar(765)
country
varchar(765)
Full-text country name. e.g.‘United States’, ‘India’,’Japan’,’Spain’...etc.
An “International” user is any country not ‘United States’

Table: teachers\_trained
A record of any teacher trained for any code.org program/course. Some teachers have been trained for multiple courses. A “trained teacher” is a teacher-user who has at least one record in this table. An “untrained teacher” is a teacher-user with no record in this table. 
teachers\_trained
Notes
user\_id
integer
Teacher user\_id
course
varchar(765)
The name of the training program. One of: csf | csd | csp | csa
trained\_at
timestamp
The point at which they are considered “trained” for the given course.
물어보세요: 몇 가지 주요 메트릭의 빠른 조회를 지원하는 데이터 웨어하우스 솔루션을 설계하세요. (자세한 내용은 아래 참조)이 과제를 위해 제출할 내용:어떤 열 또는 필드가 포함될지, 테이블이 서로 어떻게 관련되는지(또는 그렇지 않은지), 테이블이 원본 데이터와 어떻게 관련되는지 보여주는, 만들 테이블에 대한 설명 및/또는 다이어그램입니다. (테이블 이름과 테이블에 포함된 내용을 전달하는 것으로 충분하며, 모든 필드를 일일이 나열할 필요는 없습니다.) 참고: 세련되고 전문적인 다이어그램을 기대하는 것은 아닙니다. 아이디어를 전달할 수 있는 작업 스케치면 충분합니다. 종이에 자유롭게 그리고 사진을 찍어도 됩니다(가장 빠른 방법이 있다면!).소스 테이블에서 디자인에 있는 테이블 중 하나 이상을 생성하기 위한 SQL을 작성합니다.제공된 비즈니스 질문 중 하나에 답하기 위해 디자인에 대해 실행할 수 있는 SQL 쿼리를 작성합니다.참고: SQL은 의사 코드 "연필과 종이" 연습을 위한 것입니다. 정확한 구문이나 특정 SQL의 미묘한 기능에 대한 지식을 평가하는 것이 아니라, 작성하는 코드에서 알 수 있듯이 디자인을 정확하게 전달할 수 있는 능력을 평가합니다. 우리는 구문이 아닌 의미적 정확성을 찾고 있습니다. 지원자가 내린 가정과 중요하다고 생각하는 기타 고려 사항을 포함하여 설계 선택에 대한 간략한 서면 정당화. (이 정보 중 일부는 1번 답변에 이미 포함되어 있을 수 있으며, 이 경우 여기서 반복하지 않아도 됩니다).제출 방법:4개의 과제 모두에 대한 답변을 하나의 문서 또는 슬라이드 자료(Google 문서 또는 Google 슬라이드가 선호되지만 docx 또는 pptx도 괜찮습니다.)에 담습니다.과제 자료의 모든 곳에서 본인의 이름을 삭제합니다. (작업 결과물을 평가할 때 편견을 피하기 위해 비식별화된 상태로 검토합니다.)작업 세부 정보배경: 팀의 데이터 분석가와 조직의 초보 SQL 사용자는 비즈니스 메트릭을 조회하고 아래에 설명된 비즈니스 질문에 답할 수 있는 빠른 방법이 필요합니다. 분석 엔지니어의 임무는 프로덕션 데이터베이스에서 로드된 소스 테이블 집합을 기반으로 이러한 메트릭을 지원하는 분석 테이블의 디자인을 제안하는 것입니다. (프로덕션 테이블에 대한 자세한 내용과 정보는 아래에 나와 있습니다.)지원해야 하는 비즈니스 지표는 다음과 같습니다.메트릭 이름비즈니스 정의주간 활성 사용자 수지난 7일 이내에 한 번 이상 로그인한 사용자 수월간 활성 사용자지난 30일 이내에 최소 한 번 이상 로그인한 사용자 수연간 활성 사용자지난 365일 이내에 한 번 이상 로그인한 사용자 수역대 주간 활성 사용자2013년 이후 매년 매주(주 번호 1, 2, 3...52까지) 한 번 이상 로그인한 고유 사용자 수입니다.과거 월간 활성 사용자 수2013년 이후 매년 월별(월 번호 1, 2, 3...12까지)로 한 번 이상 로그인한 고유 사용자 수입니다.연간 연간 활성 사용자 수2013년 이후 매년 한 번 이상 로그인한 고유 사용자 수입니다.이러한 지표에서 지원해야 하는 세그먼트입니다:학생 대 교사 계정(users.user\_type 참조)미국 대 해외 사용자(참조: user\_geos.country)교육받은 교사 대 교육받지 않은 교사(참조: teachers\_trained)비즈니스에서 흔히 받을 수 있는 몇 가지 질문입니다:지난 5년간 교사와 학생 모두의 월별 활성 사용자 수는 어떻게 되나요?이번 달에는 활동했지만 이번 주에는 활동하지 않은 사용자(즉, 7~30일 전에 활동한 사용자)를 식별합니다.지난 3년 동안 한 달 동안만 활동한 사용자의 수는 몇 명입니까?미국 대 해외 교사의 평균 활동 월 수는 얼마인가요?교육을 받은 교사가 교육을 받지 않은 교사보다 일반적으로 더 오래 활동하나요?작년 어느 시점에 활동했던 사용자 중 이번 달에 활동하는 사용자는 몇 명인가요?데이터 테이블: 아래 다이어그램과 설명에 제시된 4개의 테이블(Redshift 데이터베이스에 로드됨)을 고려하여 과제를 완료해야 합니다. 이 다이어그램은 테이블 간의 몇 가지 주요 관계를 보여줍니다. 주요 연결은 사용자 ID를 통해 이루어지지만 일대다 및 다대다 관계에 유의하세요.테이블: 사용자각 사용자에 대한 정보 테이블입니다. user.id당 하나의 행사용자참고id정수다른 테이블의 user\_id 필드와 일치합니다.사용자 이름varchar(255)이름varchar(255)birthday타임스탬프사용자\_typevarchar(48)다음 중 하나입니다: '학생' 또는 '교사'created\_at타임스탬프이 사용자 기록이 생성된 시점업데이트됨\_at타임스탬프이 사용자 기록이 가장 최근에 업데이트된 시기테이블: sign\_ins웹사이트에 성공적으로 로그인한 모든 사용자 기록. 사용자 로그인당 한 행입니다.sign\_ins참고id정수사용자\_id정수sign\_in\_at타임스탬프서명\_in\_count정수이 사용자의 누적 총 로그인 횟수(1-n)테이블: user\_geos사용자와 연결된 지리적 위치 IP 주소의 레코드입니다. 사용자\_id당 하나의 레코드입니다.user\_geos참고id정수사용자\_id정수created\_at타임스탬프이 사용자에 대한 이 위치 정보 레코드가 처음 생성된 시점업데이트된\_at타임스탬프이 지리적 레코드가 가장 최근에 업데이트된 시기IP\_주소varchar(765)국가varchar(765)Full-t