ANALYST GUIDEBOOK_
Section 1 — Understanding eWards, Our Products & The
Analyst Role
1. About eWards?
eWards is a Saas based company that builds smart, data-driven tools to help
businesses — especially retail brands, restaurants, and service outlets —
understand their customers, reward loyalty, and communicate more effectively.
Think of eWards as the “brain” behind a brand’s customer relationships.
Our platform powers many everyday interactions such as:
● Receiving a birthday message from a favourite store
● Earning loyalty points while shopping
● Getting a coupon or feedback link after checkout
All of that happens because eWards quietly connects the shop’s billing, customer,
and messaging systems to deliver a seamless experience.
In simple words —
eWards helps brands remember their customers and make every visit count.
2.What are our Products?
eWards offers four core products that work together like the four gears of a
complete customer-engagement engine.
Product What It Does (in Simple Words) Example You Can Relate To
Ace A Loyalty CRM System — keeps track of every Imagine you visit a clothing store and earn 100
customer, their visits, and the points or rewards points. Next time you shop, those points
they’ve earned. automatically reduce your bill. Ace records and
manages those points securely.
Ping A Communication Engine that sends You get a “Happy Birthday!” message from a
automated messages via WhatsApp, SMS, or café with a ₹200 coupon or a “Flash Sale
Email. today!” alert — Ping is the tool that sends it.
Loop An All-in-One Feedback Management System After dining at a restaurant, you receive a
that automates omni-channel feedback WhatsApp feedback link asking you to rate
collection (68 % of users prefer WhatsApp) and your experience. The data goes into Loop,
provides real-time analytics to improve which analyses responses to help the brand
customer journeys. improve.
InsightX A Data & Analytics Dashboard that gives If a brand wants to know which stores perform
businesses a clear picture of customer trends best or how many customers stopped visiting,
and performance. InsightX presents those insights in easy charts
and numbers.
3.What is CRM (Customer Relationship Management)?
CRM is a system that helps a business manage every interaction with its customers —
from their first visit to their latest purchase.
Example:
If a salon remembers your name, your last appointment, and sends you a “20 % off
this month” message — that’s CRM in action.
A CRM like Ace helps the salon automatically track and act on that information.
➡ CRM = Customer Memory + Personalised Communication + Retention
4.What is POS (Point of Sale)?
A POS system is the billing or checkout software that records every sale.
When you buy something at a store, the cashier uses the POS to:
● Add your purchased items
● Calculate totals and taxes
● Generate a bill (printed or digital)
Example:
When you get a bill showing your items, total amount, and phone number, that’s the
POS at work.
This data later flows into eWards to credit loyalty points or send offers.
➡ POS = The Store’s Cash Register + The Source of Transaction Data
5.What is an Integration?
Integration means connecting two systems so they can automatically share data.
When systems such as POS, CRM, and Messaging Tools are integrated, they
communicate in real time.
Example:
● The POS knows what you bought.
● The CRM (eWards) knows who you are.
● Through Integration, data flows instantly from POS → eWards.
This allows eWards to:
● Add loyalty points right after billing
● Send confirmation or feedback messages
● Trigger coupons or offers instantly
So when you get your reward SMS or feedback link within seconds, that’s the power
of integration.
6.What is an API & API Integration?
API stands for Application Programming Interface.
It is a digital bridge that allows two different systems to talk to each other securely
and automatically.
In simple words:
● An API defines what data can be shared (for example: customer ID, bill
amount).
● API Integration connects these bridges so data flows from one system (like
POS) into another (like eWards) in real time.
Example:
When a customer’s bill is generated in the POS, an API call instantly sends that bill
information to eWards.
eWards then processes it → issues points → sends messages through Ping → and
shows results on InsightX.
So, APIs are the highways that carry information between systems.
7.What is a Database?
A Database is like a digital warehouse where all data is stored, organised, and
retrieved when needed.
At eWards, our databases contain millions of rows of information — customers, bills,
points, campaigns — securely structured for quick access and reporting
Example:
When you open InsightX to see “Sales this Month”, it runs an SQL query on the
database to fetch that information from all merchant transactions.
➡ Database = Where All the System’s Memory Lives.
8.About the Analyst Role
At eWards, an Analyst is the bridge between business, clients, and technology.
The Analyst ensures that the client’s idea becomes a correctly designed, well-tested,
and clearly documented product feature.
What Does an Analyst at eWards Do?
1. Requirement Translator & Planner
○ Understands client requirements and translates them into clear, actionable
steps.
2. Logic & Flow Designer
○ Defines how the product should behave — e.g., “When a customer shops
₹1000, give 100 points” or “Send feedback link 2 hours after purchase.”
3. Data Mapper
○ Designs how information moves between systems (POS → API → eWards →
Ping → InsightX).
4. Coder for Documentation
○ Writes SQL queries, API examples, and PHP/Python logic snippets inside
documentation so that developers clearly understand the implementation.
5. Tester & Validator
○ Uses tools like Postman to test APIs and verifies that integrations and data
flows work correctly.
6. Collaborator
○ Works with backend (Laravel), frontend (Vue/React), UI/UX designers, and
QA teams to deliver stable features.
7. Analyst & Reporter
○ Uses Excel, SQL, and InsightX to measure campaign results, find trends, or
validate system accuracy.
Key Responsibilities
Domain Meaning in Daily Work Example
Requirement Attend client or POS-vendor calls to “Will your POS send us customer mobile
Gathering capture data flow & logic needs. numbers with each bill?”
Documentation Prepare BRDs, DB schemas, API mappings, Drafting “Referral Program Flow” with sample
and logic notes (with code snippets). SQL and API payloads.
Testing Validate data & API responses using Confirm that loyalty issuance API calculates
Postman. correctly.
Collaboration Coordinate between multiple tech teams Ensure backend fix aligns with frontend
for smooth delivery. display.
Analysis Run SQL/Excel checks to measure Identify merchants with high
campaign or merchant performance. repeat-customer rate.
Product Thinking Suggest improvements to enhance Propose auto-feedback trigger after
usability or accuracy. redemption.
Why This Role Matters
An Analyst ensures that every moving part of the eWards ecosystem — from POS
integrations to campaign analytics — works precisely and reliably.
You will:
● Understand how data flows across APIs and databases.
● Write clear logic and code snippets that developers can directly implement.
● Validate integrations and workflows end-to-end.
● Analyse results to make products smarter and clients happier.
In short, Analysts connects ideas → logic → code → outcome.
Section 2 — Understanding Technical Concepts
1. What is a Database, Table, and Schema?
● Database → A structured collection of data.
● Table → Stores related data in rows & columns.
● Schema → Logical container that groups related tables, views, and procedures.
Think of it as a “folder” inside the database.
2. Primary Key vs Foreign Key
Concept Meaning
Primary Key Unique identifier within a table. Cannot be NULL.
Foreign Key A foreign key is a column that creates a
relationship between two tables, linking the
foreign key in one table to the primary key in
another.
3. Normalization (1NF → 3NF)
- Normalization = Normalization is the process of organizing data to minimize
redundancy and improve data integrity like arranging data so there’s no repetition .
● 1NF: Every column holds atomic (single) values.
● 2NF: Remove partial dependencies — non-key columns depend entirely on the key.
● 3NF: Remove transitive dependencies — no column depends on another non-key
column.
4. Joins (Connecting Tables)
Join Type Use Simple Example
INNER JOIN Return only matching records. Find customers who made
purchases.
LEFT JOIN Keep all from the left table. List all customers, even if there is no
bill.
RIGHT JOIN Keep all from the right table. List all bills, even if a customer
deleted.
FULL JOIN Combine both. See every record, matched or not.
5. Indexes — When and Why to Use
- Indexes act like bookmarks in a book — they speed up data search.
● Create them on columns used often in WHERE, JOIN, or ORDER BY.
● Downside: extra memory + slower writes.
➡ Example: CREATE INDEX idx_customer_date ON pos_billing_dump(customer_id,
bill_date);
6. Constraints — Rules that Protect Data
Constraint Purpose Example
NOT NULL Disallow empty values. customer_id NOT NULL
UNIQUE No duplicate values. merchant_code UNIQUE
DEFAULT Auto-fill value if none provided. status DEFAULT 'active'
7. ER Diagram Basics
- An Entity-Relationship (ER) Diagram shows how tables link.
Example in eWards:
CUSTOMER_MASTER 1 ────∞ BILLING_DUMP 1 ────∞ BILLING_ITEM_DUMP
This means one customer → many bills; one bill → many items.
8. OLTP vs OLAP
Type Purpose
OLTP Real-time transactional processing.
OLAP Analytical queries, aggregates.
9. How do you add a new column to an existing table?
- `ALTER TABLE table_name ADD column_name datatype;`
10. What is the difference between WHERE and HAVING
clauses?
- WHERE is used to filter rows before grouping, while HAVING is used to filter
groups after aggregation.
11. How do you fetch unique records from a table?
- `SELECT DISTINCT column_name FROM table_name;`
12. What is the difference between VARCHAR, CHAR, and
FLOAT?
VARCHAR
- VARCHAR stands for "variable character" and is used to store variable-length
strings.
- It allows you to store strings of varying lengths, up to a maximum specified length.
- The storage space used by VARCHAR columns depends on the actual length of
the data stored in them.
- For example, VARCHAR(50) can store strings of up to 50 characters in length, but
if you only store a 10-character string in it, it will only use enough space to store
those 10 characters.
CHAR
- CHAR stands for "character" and is used to store fixed-length strings.
- It allocates a fixed amount of storage space for each value, regardless of the
actual length of the stored data.
- If the stored string is shorter than the defined length, it will be padded with
spaces to fill the allocated space.
- For example, CHAR(10) will always occupy 10 characters of storage, whether you
store a 3-character string or a 10-character string.
FLOAT
- FLOAT is a data type used to store approximate numeric values with a decimal
point.
- It is typically used for scientific or engineering calculations where precision to a
certain number of decimal places is needed but exact precision is not crucial.
- FLOAT values are approximate because they are represented in computer
memory using a floating-point representation, which may introduce rounding errors.
- FLOAT can store a wide range of values, including both very small and very large
numbers, but the precision is limited.
- For example, FLOAT(8,2) might be used to store a number with up to 8 digits,
including 2 decimal places.
13. Explain the difference between UNION and UNION
ALL.
- UNION combines the result sets of two queries and removes duplicates, while
UNION ALL includes duplicates.
14. What is a trigger in SQL?
- A trigger is a set of SQL statements that automatically executes in response to
certain events on a particular table or view.
15. Explain the difference between DELETE and
TRUNCATE and DROP
- DELETE removes specified rows and can have a WHERE clause, while
TRUNCATE removes all rows in a table and is faster as it doesn't log individual
row deletions.
DELETE removes rows from a table based on a condition, TRUNCATE
removes all rows but keeps the structure, and DROP deletes the table
structure entirely.
16. How do you optimize a SQL query?
- By using indexes, avoiding unnecessary columns in SELECT, optimizing joins,
using query hints, and analyzing execution plans.
17. What is a Recursive Function?
- A recursive function is a function that calls itself in its definition. It is used to
solve problems that can be broken down into smaller, similar subproblems.
Each recursive call should bring the function closer to a base case, which
stops the recursion.
18. Write an SQL query to find the second highest salary
from an employees table.
Method 1 - SELECT MAX(salary) AS second_highest_salary FROM
employees WHERE salary < (SELECT MAX(salary) FROM employees);
Method 2- SELECT DISTINCT salary FROM employees ORDER BY
salary DESC LIMIT 1 OFFSET 1;
19. Write a query to get the total number of employees in
each department.
- SELECT department, COUNT(*) AS total_employees FROM employees
GROUP BY department;
20. How do you create an index on a table?
- `CREATE INDEX index_name ON table_name (column_name);`
21. Write a query to get the cumulative sum (For each
employee, the cumulative sum is the sum of their salary
and the salaries of all employees hired before them) of
salaries for each employee ordered by their hire date.
- SELECT employee_id, employee_name, salary, SUM(salary) OVER (ORDER BY
hire_date) AS cumulative_salary FROM employees;
22. How do you retrieve the last record in a table?
- SELECT * FROM table_name ORDER BY id DESC LIMIT 1;
23. Retrieve the names of customers who have placed
orders for products with a price greater than '1000'.
Tables required — customers: Contains customer information, including customer
IDs and names. orders: Contains order information, including order IDs and customer
IDs. order_items: Contains information about products ordered, including order IDs,
product IDs, and quantities. products: Contains product information, including
product IDs and prices.
Columns required : [Link]: The name of the customer.
orders.customer_id: The customer ID associated with the order.
order_items.order_id: The order ID associated with the order item.
order_items.product_id: The product ID associated with the order item.
products.product_id: The product ID of the product. [Link]: The price of
the product.
ANSWER--
SELECT [Link]
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
INNER JOIN order_items ON orders.order_id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.product_id
WHERE [Link] > 1000;
24. Find the total sales amount for each product.
Tables: products: Contains product information, including product IDs and names.
order_items: Contains information about products ordered, including order IDs,
product IDs, quantities, and prices.
Columns: [Link]: The name of the product. order_items.product_id: The
product ID associated with the order item. order_items.quantity: The quantity of the
product ordered. order_items.price: The price of the product.
ANSWER-
SELECT [Link], SUM(order_items.quantity * order_items.price) AS
total_sales FROM products INNER JOIN order_items ON products.product_id
= order_items.product_id GROUP BY [Link];
25. How can you identify and delete orphan records
(records without a corresponding parent) in a child table?
- DELETE FROM child WHERE parent_id NOT IN (SELECT parent_id FROM
parent);
Explanation: DELETE FROM child: This specifies that we are deleting records
from the child table.
WHERE parent_id NOT IN (SELECT parent_id FROM parent): This checks for
child records whose parent_id does not exist in the parent table. These are
the orphan records that will be deleted.
26. You are given a table with the following columns:
customer_id, visit_date, and bill_amount.Write a SQL
query to identify customers who have visited more than
once in the same calendar month
- SELECT customer_id, DATE_FORMAT(visit_date, '%Y-%m') AS visit_month,
COUNT(*) AS visit_count FROM visits GROUP BY customer_id, visit_month
HAVING COUNT(*) > 1;
27. To identify Dormant Customers, Write a query for the
customers with no orders in the last 120 days but ≥2
historical orders.
- SELECT customer_id, COUNT(*) AS total_orders, MAX(order_date) AS
last_order_date
FROM orders WHERE status = 'PLACED' GROUP BY customer_id HAVING
COUNT(*) >= 2 AND MAX(order_date) < CURRENT_DATE - INTERVAL '120 DAY';
28. How do you ensure data integrity in a relational
database?
- Data integrity is ensured through constraints, such as:
○ Primary key constraints: Ensure each record has a unique identifier.
○ Foreign key constraints: Ensure relationships between tables are
maintained.
○ Unique constraints: Ensure that all values in a column are unique.
○ Check constraints: Ensure that values in a column meet specific
conditions.
○ Not null constraints: Ensure that a column cannot have a NULL value.
29. What Is Hypothesis Testing in Statistics?
- Hypothesis Testing is a type of statistical analysis in which you put your
assumptions about a population parameter to the test. It is used to estimate the
relationship between 2 statistical variables.
30. You need to generate a report showing customer
retention cohorts over a 6-month window. Write a SQL
query
- Explanation:
A cohort is a group of users who signed up (or made their first purchase) in
the same month.
You track how many of them return in future months.
Approach:
WITH first_orders AS (
SELECT customer_id, MIN(DATE_TRUNC('month', order_date)) AS cohort_month
FROM orders
GROUP BY customer_id
),
orders_with_cohort AS (
SELECT o.customer_id,
DATE_TRUNC('month', o.order_date) AS order_month,
f.cohort_month,
DATE_PART('month', AGE(DATE_TRUNC('month', o.order_date), f.cohort_month))
AS month_offset
FROM orders o
JOIN first_orders f ON o.customer_id = f.customer_id
)
SELECT cohort_month, month_offset, COUNT(DISTINCT customer_id) AS
retained_users
FROM orders_with_cohort
GROUP BY cohort_month, month_offset
ORDER BY cohort_month, month_offset;
31. A dashboard is running slow due to a complex SQL
query joining 5 tables. How would you diagnose and fix
performance issues?
- Answer:
Use EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN (MySQL) to see:
■ If full table scans are happening
■ Where joins are taking time
Common fixes:
○ Add indexes on JOIN/WHERE columns
○ Use CTEs or temp tables to break logic into steps
○ Avoid using SELECT *
○ Add filters earlier in subqueries
○ Consider materialized views for recurring calculations
The goal is to reduce unnecessary computation by the database engine and
ensure indexes are used properly.
Section 3 — Understanding Excel Basics
1.What is a pivot table, and how is it used in Excel?
- A pivot table is a data summarization tool in Excel that allows users to
organize, filter, and analyze large datasets. It is used to create dynamic
reports that can show trends and comparisons.
2.What is an exploratory data analysis (EDA)?
- EDA is the process of analyzing datasets to summarize their main
characteristics, often using visual methods. It helps to uncover patterns,
spot anomalies, and test hypotheses.
3.How do you use the CONCATENATE function, and
what is its purpose?
- The CONCATENATE function joins two or more text strings into one
string. The syntax is =CONCATENATE(text1, text2, ...)
4.How do you use the OFFSET function in Excel?
- The OFFSET function returns a reference to a range that is a specified
number of rows and columns from a cell or range of cells. The syntax is
=OFFSET(reference, rows, cols, [height], [width]).
5.How do you use the FORECAST function in Excel?
- The FORECAST function predicts future values based on existing data.
The syntax is =FORECAST(x, known_y's, known_x's).
6.How do you create and use slicers in Excel?
- Slicers are visual tools for filtering data in PivotTables and PivotCharts.
Create them by selecting your PivotTable, going to the 'Insert' tab, and
choosing 'Slicer.' Use them to filter data interactively.
7.What is the purpose of the Excel TRIM function?
- The TRIM function removes all extra spaces from a text string, leaving
only single spaces between words. The syntax is =TRIM(text).
8.What is the difference between VLOOKUP and
HLOOKUP?
- VLOOKUP: Searches vertically in a column.
- HLOOKUP: Searches horizontally in a row.
9.How can you protect an Excel sheet or specific cells?
1. Select cells → Right click → Format Cells → Protection → Lock.
2. Go to Review → Protect Sheet and set a password.
10. What is Conditional Formatting?
- It visually highlights data based on conditions.
11. How to find total revenue by outlet using PivotTable?
1. Select data → Insert → PivotTable
2. Drag Outlet Name → Rows
3. Drag Revenue → Values → SUM
Shows each outlet’s total revenue at a glance
12. How do you combine two or more text columns?
- Use:
=CONCATENATE(A2, " ", B2)
13. How do you find duplicate entries in a list?
- Use Conditional Formatting:
Select range → Home → Conditional Formatting → Duplicate Values.
14. How to use the COUNTIFS and SUMIFS functions?
- COUNTIFS → counts with multiple conditions.
=COUNTIFS(B:B, ">1000", C:C, "Kolkata")
Count customers with spend > 1000 in Kolkata.
SUMIFS → sums with multiple conditions.
=SUMIFS(D:D, B:B, ">1000", C:C, "Kolkata")
15. How do you clean and prepare data using Power
Query?
- Go to Data → Get & Transform (Power Query Editor).
You can:
○ Remove duplicates
○ Split columns
○ Change data types
○ Merge or append multiple sheets
Section 4 — Logical & Critical Thinking Practice
1. Yesterday the POS–eWards API failed for all merchants.
What are your first 3 checks?
-
● API Status & Error Logs – Did the endpoint time out or return 500
errors?
● Database Entries – Were transactions queued but not inserted?
● Alert or Cron Monitoring – Was any job paused or authentication token
expired?
2. A customer received two loyalty coupons for the same
bill. What will you do?
-
○ Verify if the payload or API call was triggered twice (idempotency
issue).
○ Check bill_no + merchant_id + customer_id combination for duplicates.
○ Identify affected records → reverse extra issuance in loyalty_ledger.
3. Client says: “Yesterday’s dashboard shows zero
revenue.” Or, “Today’s sales are not showing in the
dashboard.” What are your first steps?
-
● Check Integration Health: See if the API from POS is successfully
hitting our endpoint today.
● Database Verification: Open the reporting table (pos_billing_dump)
— verify if today’s data exists.
● Cron / ETL Check: Confirm if the daily data transfer job to InsightX ran
successfully.
● Communicate Early: Inform the client that you’re validating source data
and update ETA for fix.
4. A merchant complains that the “Buy for ₹1000 and get
₹200 coupon” campaign didn’t trigger. How will you
debug it?
-
● Verify the campaign setup in Ping (correct condition: bill_amount >=
1000).
● Check if the trigger event from POS was received in API payload.
● Review time frame & eligibility — campaign active during the
purchase?
● Confirm if the coupon logic table has any duplicate or paused cron
job.
5. Client reports customers being issued points twice for
the same bill. How do you proceed?
-
● Fetch the bill_no, merchant_id, customer_id combination from
loyalty_ledger.
● Identify duplicates using:
SELECT bill_no, COUNT(*) FROM loyalty_ledger WHERE
transaction_type='EARN'GROUP BY bill_no HAVING COUNT(*)>1;
● Check API logs — was payload pushed twice?
● Validate if unique_transaction_id was missing in payload.
FOR ANY QUERY REGARDING THE ABOVE QUESTIONS -
PLEASE REFER BELOW GPT LINK—
[Link]
1