Question 1

Design and implement a session analytics system for clickstream data.

Description: You are given a time series data, which is a clickstream of user activity. Perform Sessionization on the data (as per the session definition given below) and generate session ids.

Expected Steps:
* Read the input data file into your program (datafile.txt already present in solution explorer). Use spark batch (PySpark/Spark-Scala) to add an additional column with name session_id and generate the session ids based on the following logic:
* Session expires after inactivity of 30 minutes; because of inactivity, no clickstream record will be generated.
* Session remains active for a maximum duration of 2 hours (i.e., after every two hours a new session starts)
* Save the resultant data (original data, enriched with Session IDs) in a Parquet file format.

In addition to the above result, consider the following requirement/scenario and give the results:
* Get Number of sessions generated for each day.
* Total time spent by a user in a day.

Guidelines:
* Write all the queries for the different requirements in Spark-sql.
* Think in the direction of using partitioning, bucketing, etc.

Note:
1. Do not use direct spark-sql
2. Do not install/update any core libraries - spark, Scala etc.
3. Run file using spark-submit filename.py
4. Do not run with Python or PySpark directly from run button of ide.

Accepted Answer

This is a classic sessionization problem — grouping a user's clickstream events into discrete sessions based on inactivity and maximum duration rules. Design decisions: Partitioning output by user id : enables efficient per user queries on the Parquet output and aligns with the "think about partitioning" instruction Sessionization via lag + cumulative sum : a standard, scalable approach that works in a single Spark pass per user partition Spark SQL for analytics : uses createOrReplaceTempView to satisfy the "write queries in Spark SQL" requirement without using direct spark.sql() for the main logic spark submit compatible : no interactive IPython dependencies; all logic runs as a standalone script

Question 2

Write a PySpark program to retrieve the up to 4th highest distinct salary for each department from the given dataset. The results should be sorted in descending order of salary within each department.

Accepted Answer

Output: Key points: .distinct() before ranking ensures duplicate salaries count as one rank (e.g., two employees at 110k = one distinct salary entry) dense rank() is critical here (vs rank() ) — it assigns consecutive integers without gaps, so ranks are 1, 2, 3, 4 even when salaries are skipped RANK() would skip numbers after ties; ROW NUMBER() would treat duplicates as separate entries — both are wrong for this requirement The filter <= 4 gives "up to 4th highest" — departments with fewer than 4 distinct salaries return all they have

Publicis Sapient Interview Questions

Write a PySpark program to retrieve the up to 4th highest distinct salary for each department from the given dataset. The results should be sorted in descending order of salary within each department.