See all of a given experiment participant's work

[brachbach]

For purposes of:

• us evaluating the participant
• the participant learning from how things went in the rest of the tree after they did their workspace

I think a simple and totally adequate version of this would be a list of all the workspaces that the participant worked on, most recent at the top, each with links to both the history view for that workspace and the compact tree view with that workspace highlighted

One way I imagine using this is copying all of the workspace URLs into a spreadsheet in one column and then evaluating them in another column, so the view should somehow make it easy to copy all of the URLs

We could consider something fancier than just the list of links if it seems pretty easy to do

[brachbach]

Probably as part of work on this issue, we'd also like to be able to see all workspaces done by all participants in reverse chronological order, as a way of seeing what's happening in experiments

So basically the same thing as the per-participant thing mentioned here, but not filtered by participant.

[brachbach]

Also, now that I think about it, we'd want some summary statistics:

• win percentage as malicious expert
• win percentage as judge
• average time per workspace, ideally broken down by role

would also be sort of nice to be able to specify a date range for which assignments to return

[matiroy]

Indeed, that would be pretty useful to give feedback. Actually, I've started extracting it directly from the DB to be able to do so. I currently use the following query, but also want to add: isCompleted, winner, completedDate.

-- 2019-08-27 Mati

SELECT DISTINCT CONCAT('https://mosaic.ought.org/w/', Workspaces."serialId", '/compactTree?expanded=true'),
                CONCAT('https://mosaic.ought.org/w/', RootWorkspaces."serialId", '/compactTree?expanded=true')
FROM public."Users" Users
INNER JOIN public."Snapshots" Snapshots ON Snapshots."userId" = Users.id
INNER JOIN public."Workspaces" Workspaces ON Workspaces.id = Snapshots."workspaceId"
LEFT JOIN public."Workspaces" RootWorkspaces ON RootWorkspaces.id = Workspaces."rootWorkspaceId"
WHERE Users."givenName" LIKE CONCAT('%<givenName>%')
LIMIT 100;

Google Doc ref: https://docs.google.com/document/d/1X0PKNmJ8pvPbqshrYrDYv_YQ1mvIe1HUuk9kJjOYCx0/

[matiroy]

Updated script:

-- Author: Mati Roy | Created: 2019-08-27 | Updated: 2019-08-30
SELECT DISTINCT
	date_trunc('second', MAX(Snapshots."updatedAt")) AS date,
	CASE
		WHEN Workspaces."isEligibleForHonestOracle" = true THEN 'Honest Expert'
		WHEN Workspaces."isEligibleForMaliciousOracle" = true THEN 'Malicious Expert'
		ELSE 'Judge'
	END AS role,
	CONCAT('https://mosaic.ought.org/w/', Workspaces."serialId") AS "workspace",
	CONCAT('https://mosaic.ought.org/w/', RootWorkspaces."serialId", '/compactTree?expanded=true') AS "compact tree",
    CONCAT('https://mosaic.ought.org/w/', RootWorkspaces."serialId", '/subTree?expanded=true') AS "sub tree",
	Workspaces."timeSpentOnThisWorkspace"/60 AS "time to complete (min)"
FROM public."Users" Users
INNER JOIN public."Snapshots" Snapshots ON Snapshots."userId" = Users.id
INNER JOIN public."Workspaces" Workspaces ON Workspaces.id = Snapshots."workspaceId"
LEFT JOIN public."Workspaces" RootWorkspaces ON RootWorkspaces.id = Workspaces."rootWorkspaceId"
WHERE Users."email" LIKE '%[email protected]%'
GROUP BY 
	CASE
		WHEN Workspaces."isEligibleForHonestOracle" = true THEN 'Honest Expert'
		WHEN Workspaces."isEligibleForMaliciousOracle" = true THEN 'Malicious Expert'
		ELSE 'Judge'
	END,
	CONCAT('https://mosaic.ought.org/w/', Workspaces."serialId"),
	CONCAT('https://mosaic.ought.org/w/', RootWorkspaces."serialId", '/compactTree?expanded=true'),
    CONCAT('https://mosaic.ought.org/w/', RootWorkspaces."serialId", '/subTree?expanded=true'),
	Workspaces."timeSpentOnThisWorkspace"/60

[matiroy]

@brachbach , what about just improving 'Your Activity' page instead for that purpose? We could add the datetime (https://github.com/oughtinc/mosaic/issues/709), the WS number, a link to the WS, your role the outcome of the WS, the outcome of the tree, and maybe other things. But I think a link would be the priority.

[matiroy]

updated script:

-- Author: Mati Roy | Branched: 2019-09-01 | Updated: 2019-09-01
WITH UserWorkspace AS (
	SELECT DISTINCT
		Experiments."name" AS experimentName
		, Workspaces."serialId"
		, rootWorkspaces."serialId" AS rootWorkspaceSerialId
		, Snapshots."updatedAt"
		, CASE
			WHEN Workspaces."isEligibleForHonestOracle" = true THEN 'Honest Expert'
			WHEN Workspaces."isEligibleForMaliciousOracle" = true THEN 'Malicious Expert'
			ELSE 'Judge'
		END AS role
		, CONCAT('https://mosaic.ought.org/w/', Workspaces."serialId") AS "workspace"
		, CONCAT('https://mosaic.ought.org/w/', RootWorkspaces."serialId", '/compactTree?expanded=true') AS "compact tree"
		, CONCAT('https://mosaic.ought.org/w/', RootWorkspaces."serialId", '/subTree?expanded=true') AS "sub tree"
		, Workspaces."timeSpentOnThisWorkspace"/60 AS "time to complete (min)"
	FROM public."Users" Users
	LEFT JOIN 
		public."Snapshots" Snapshots 
		ON Snapshots."userId" = Users.id
	LEFT JOIN 
		public."Workspaces" Workspaces 
		ON Workspaces.id = Snapshots."workspaceId"
	LEFT JOIN 
		public."Workspaces" rootWorkspaces 
		ON rootWorkspaces.id = Workspaces."rootWorkspaceId"
	LEFT JOIN
		public."Trees" Trees
		ON Trees."rootWorkspaceId" = Workspaces."rootWorkspaceId"
	LEFT JOIN
		public."ExperimentTreeRelation" ExperimentTreeRelation
		ON ExperimentTreeRelation."TreeId" = Trees.id
	LEFT JOIN
		public."Experiments" Experiments
		ON Experiments.id = ExperimentTreeRelation."ExperimentId"
	WHERE 
		Users."email" LIKE '%allison%'
),
completedWorkspaces AS (
	SELECT DISTINCT 
		rootWorkspaces."serialId"
	FROM public."Workspaces" Workspaces 
	LEFT JOIN
		public."Workspaces" rootWorkspaces
		ON rootWorkspaces.id = Workspaces."rootWorkspaceId"
	LEFT JOIN
		public."Trees" Trees
		ON Trees."rootWorkspaceId" = Workspaces."rootWorkspaceId"
	LEFT JOIN
		public."ExperimentTreeRelation" ExperimentTreeRelation
		ON ExperimentTreeRelation."TreeId" = Trees.id
	LEFT JOIN
		public."Experiments" Experiments
		ON Experiments.id = ExperimentTreeRelation."ExperimentId"
	WHERE 
		Workspaces."parentId" = Workspaces."rootWorkspaceId"
		AND Workspaces."isCurrentlyResolved" = true
)
SELECT DISTINCT
	experimentName
	, UserWorkspace."serialId" AS "Workspace"
	, UserWorkspace.rootWorkspaceSerialId AS "Root Workspace"
	, CASE WHEN completedWorkspaces."serialId" = UserWorkspace.rootWorkspaceSerialId THEN 'yes' ELSE 'no' END AS "Is Completed?"
	--, rootWorkspaceSerialId
	, date_trunc('second', MAX(UserWorkspace."updatedAt")) AS date
	, role
	, "workspace"
	, "compact tree"
	, "sub tree"
	, "time to complete (min)"
FROM UserWorkspace
LEFT JOIN completedWorkspaces
	ON completedWorkspaces."serialId" = UserWorkspace.rootWorkspaceSerialId
--WHERE completedWorkspaces."serialId" = UserWorkspace.rootWorkspaceSerialId IS true
GROUP BY
	experimentName
	, UserWorkspace.rootWorkspaceSerialId
	, completedWorkspaces."serialId" = UserWorkspace.rootWorkspaceSerialId
	, UserWorkspace."serialId"
	, rootWorkspaceSerialId
	, role
	, "workspace"
	, "compact tree"
	, "sub tree"
	, "time to complete (min)"
ORDER BY UserWorkspace.rootWorkspaceSerialId

output example: https://docs.google.com/spreadsheets/d/1GAqwZj0PX1a5gE6ya78Nsl87DdRdTny5QHtwIZjtWaA/