Min/Max Attempt

[blpetosa]

Hello Everyone,

I am trying to get the first and last attempts for surveys . Right now a user could take a survey 6 times, but I only want their 1st, 6th, or both of those attempts. I need their first and last response per segment_id, otherwise I would lose the other quiz_id's/segment_id's they engaged with.  Bellow, is the table that logs all our survey data.

Quiz:
LOAD quiz_id,
quiz_name,
quiz_date,
segment_id,
user_id,
site_id as quiz_site,
test_type,
question,
question_order,
answer_order as order,
answer_or_sub_question,
question_id,
solution_id,
solution_answer_id_provided_by_user,
subanswer_provided_by_user,
explain_answer_provided_by_user,
answer_provided_by_user_was_correct,
quiz_passed_by_user,
quiz_completed_by_user,
answer_provided_by_user,
quiz_type,
answer_order_provided_by_user,
description
;

Here was my attempt at it:

RIGHT JOIN (Quiz)

first_last:
LOAD
*,
min(quiz_date) as first_attempt,
max(quiz_date) as last_attempt

RESIDENT Quiz
WHERE (min(quiz_date) = quiz_date OR max(quiz_date) = quiz_date)
GROUP BY user_id, quiz_id, segment_id
;

I think I may need something similar to a self join here since the WHERE should only apply to a user within a quiz_id/segment_id. I am getting a very vague "general load error" when running this. I appreciate any help.

Thanks,

Ben

[pasi_lehtinen · Accepted solution]

Please try this:

Quiz:
LOAD quiz_id,
quiz_name,
quiz_date,
segment_id,
user_id,
site_id as quiz_site,
test_type,
question,
question_order,
answer_order as order,
answer_or_sub_question,
question_id,
solution_id,
solution_answer_id_provided_by_user,
subanswer_provided_by_user,
explain_answer_provided_by_user,
answer_provided_by_user_was_correct,
quiz_passed_by_user,
quiz_completed_by_user,
answer_provided_by_user,
quiz_type,
answer_order_provided_by_user,
description
;

 

first_last:
NoConcatenate LOAD
user_id, quiz_id, segment_id,
min(quiz_date) as first_attempt,
max(quiz_date) as last_attempt
RESIDENT Quiz
GROUP BY user_id, quiz_id, segment_id
;

left join(first_last):
Load * Resident Quiz;

drop table Quiz;

[blpetosa]

Sorry for the late response, especially after your quick one. I have been OOO. This worked really well for what I needed. I did need more of a tag mechanism, but I didn't communicate that in the original post. Now I can query by the first or last attempt. I added this after what you sent:

first_last2:
Load
*,
if(first_attempt=solution_id,1,
if(last_attempt=solution_id,2,0)) as first_last
RESIDENT first_last
;

DROP TABLE first_last;

I originally tried a preceding LOAD, but it didn't like that with the "NoConcatenate LOAD"there. solution_id was also better to use than quiz_date, but that was my fault. Regardless, thank you for your help with this!

Thanks,

Ben