VW_IHS_PARTICIPANT_RECENT_DEVICES

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

PARTICIPANTIDENTIFIER

VARCHAR2

15

√

null

STUDYPARTICIPANTS.PARTICIPANTIDENTIFIER

Implied Constraint

R

FITBIT

VARCHAR2

200

√

null

APPLE_WATCH

VARCHAR2

200

√

null

PHONE

VARCHAR2

200

√

null

Relationships

Close relationships within degrees of separation

View Definition

with fitbit as (
	select
		PARTICIPANTIDENTIFIER
		,device as Fitbit_model
		,trunc(RECORD_DATE) as start_date
		,trunc(RECORD_DATE) as end_date
		,ROW_NUMBER() OVER (PARTITION BY PARTICIPANTIDENTIFIER ORDER BY RECORD_DATE desc) AS rn
	from FITBITDEVICES
	where type='TRACKER'
	and trunc(RECORD_DATE) >= '01-APR-2023'
),
recent_fitbit as (
	select * from fitbit where rn=1
),
phone as (
	select
		PARTICIPANTIDENTIFIER
		,DEVICEPLATFORM as Phone_Type
		,DEVICENAME as Phone_Name
		,DEVICEOSVERSION as Phone_Version
		,trunc(STARTDATE) as start_date
		,trunc(STARTDATE) as end_date
		,ROW_NUMBER() OVER (PARTITION BY PARTICIPANTIDENTIFIER ORDER BY STARTDATE desc) AS rn
	from SURVEYRESULTS
	where trunc(STARTDATE) >= '01-APR-2023'
	and DEVICEPLATFORM <> 'WebClient'
),
recent_phone as (
	select * from phone where rn=1
),
apple_watch as (
	select
		PARTICIPANTIDENTIFIER
		,Apple_Watch_Model
		,Apple_Watch_OS
		,start_date
		,end_date
		,ROW_NUMBER() OVER (PARTITION BY PARTICIPANTIDENTIFIER ORDER BY START_DATE desc) AS rn
	from apple_watch_devices
),
recent_apple_watch as (
	select * from apple_watch where rn=1
),
device_data as (
		select 
			PARTICIPANTIDENTIFIER
			,cast('1' as varchar2(1)) as device_type   --Fitbit
			,Fitbit_model as device
		from recent_fitbit
	union all
		select 
			PARTICIPANTIDENTIFIER
			,cast('3' as varchar2(1)) as device_type   --phone
			,Phone_Name as device
		from recent_phone
	union all
		select 
			PARTICIPANTIDENTIFIER
			,cast('2' as varchar2(1)) as device_type   --Apple Watch
			,Apple_Watch_Model as device
		from recent_apple_watch
),
data_pivot as ( 
    select * from device_data
		pivot (max(device)
				for	device_type in (
					'1' as Fitbit
					,'2' as apple_watch
					,'3' as phone
					)
				)
)
select 
	PARTICIPANTIDENTIFIER
	,Fitbit
	,Apple_Watch
	,phone
from data_pivot

VW_IHS_PARTICIPANT_RECENT_DEVICES

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views